I have one SQL Server database.
In this database more than 20 tables exists
that their structures is same.
I have two way for design this tables:
1.Create all tables separately(create more than 20 tables)
2.Create one table and separate each group
by type(add column type to table and assign same
value for each group)
What is better solution?
Please help me.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?
Sample Structure
table = Claim Field 1 = ClaimTypeA_ID Field 2 = ClaimTypeB_ID Field 3 = ClaimTypeC_ID Field 4 = ClaimTypeD_ID Field 5 = ClaimTypeE_ID Field 6 = ClaimTypeF_ID
The six field relate to the 6 different tables ID.
If I do this how do I store the data? put 0's in each of the claim types that are not used???
Hi,I have a question regarding best practices in database design. In arelational database, is it wise/necessary to sometimes create tablesthat are not related to other tables through a foreign Keyrelationship or does this always indicate some sort of underlyingdesign flaw. Something that requires a re evaluation of the problemdomain?The reason I ask is because in our application, the user can perform xnumber of high level operations (creating/updating projects, creating/answering surveys etc. etc.). Different users can perform differentoperations and each operation can manipulate one or more table. Thispart of the system is done and working. Now there is a requirement tohave some sort of audit logging inside the database (separate from thetext based log file that the application generates anyway). This"audit logging" table will contain high level events that occur insidethe application (which may or may not relate to a particularoperation). This table is in some sense related to every other tablein the database, as well as data that is not in the database itself(exceptions, external events etc.). For example : it might haveentries that specify that at time x user created project y, at time Auser filled out survey B, at time C LDAP server was down, At time D anunauthorized login attempt occurred etc.As I said, these seems to suggest a stand alone, floating table with afew fields that store entries regarding whats going on the systemwithout any direct relationship to other tables in the database. But Ijust feel uneasy about creating such an isolated table. Another optionis to store the "logging" information in another schema/database, butthat doubles the maintainance work load. Not really looking forward tomaintaining/designing two different schemas.I had a look at the microsoft adventureworks database schema diagramand they seem to have 3 standalong tables - AWBuildVersion, ErrorLogand DatabaseLog (unless i am reading it wrong!)Any advice, Information or resources are much appreciated.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin" And here's my companies table: "CompanyID, CompanyName, SalespersonID"
Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID."
Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database.
Anyway, I really don't know what I should be doing with this design. Any suggestions?
I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)
I am developing a Content management system of sorts and I want it to be pretty flexible. I have noticed that with web content (News, Articles, Events, FAQs) that there is a lot of similarity between these items. They all have the same basic fields: TitleSynopsisBodyStartDateEndDate (for News, Articles, FAQs start and end date can be used for content expiration. For events, they are used for the actual event dates.) But for some types of content, I will need fields specific to that type. For instance, I want to have a few custom settings for a "Photo Gallery" type, like, how many rows/columns of thumbnails to display per page.
I feel like I have 3 options, but would really appreciate your advice. I created diagrams for the 3 options, located here: http://nontalk.com/dbdesign/
I'm designing a database with 3 tables called Function, Test andScene.A Function has multiple Tests, but a Test has only one Function. Amany to many relationship exists between Test and Scene therefore Ineed a junction table between these two tables - giving 4 tables intotal. The Test table would store a foreign key, the primary key ofthe Function table.There is a problem with design though and that is that Functions andScenes are actually defined before the Test is defined. Therefore itshould be possible to create a Function and add to id its Scenes,before Tests have been defined. In other words, Scenes are as much apart of a Function as they are of Tests. Tests are in fact only ofrelavence to testers. Anyway, to satisfy this scenario, a Junction boxis also needed beween Function and Scene. This creates a loop betweenall tables.Is this a good approach? Any other suggestions or advice on thematter? Any advice regarding data integrity?Thanks,Barry
i am a beginner of database design, could anyone please help me tofigure out how to make these two tables work.1) a "players" table, with columns "name", "age"2) a "teams" table, which can have one OR two player(s)a team also has a column "level", which may have values "A", "B",or "C"how do you build the "teams" table (the critical question is "do ineed to create two fields" for the maximum two possible players?")how do you use one query to display the information with the followingcolumns:"name", "age", "levelA", "levelB", "levelC" (the later three columnsare integer type, showing how many teams with coresponding level thisplayer is in).now suppose i don't have any access to sql server, i save the datainto xml, and load it into a dataset. how could you do the selectionwithin the dataset? or ahead of that, how do you specify the relationsbetween "players" and "teams".the following is the schema file i am trying to make (I still don'tknow if i need to specified the primary key... and how to buildrelation between them):<code><?xml version="1.0" ?><xs:schema id="AllTables" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="AllTables" msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Players"><xs:complexType><xs:sequence><xs:element name="PlayerID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Name" type="xs:string" minOccurs="0" /><xs:element name="Age" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element><xs:element name="Teams"><xs:complexType><xs:sequence><xs:element name="TeamID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Level" type="xs:string" minOccurs="0" /><xs:element name="Player1" type="xs:int" minOccurs="0" /><xs:element name="Player2" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema></code>
Currently i am developing a job portal in ASP 2.0, SQL Server 2005 which involves Job Seeker registration, Searching of resumes, applying for job Posting, Employer Registration, Create Job Posting, Searching for Job Seeker etc. The Job Seeker is allowed to upload a word document of size up to 500Kb which is stored in Table as varbinary.
Right now I have MemberShip/Roles in seperate database. The Job Portal Tables are in seperate Database. I was told to split the Tables so that Tables of JobSeeker are One database and Employer to another Database so that they speed up the performance.
I have several tables that bridge (thats either store id's of Job Seeker or Employer) like Job Postings applied, Saved Postings Job Seeker, Job Postings of the Employer, Job Posting (Applied ones) alert etc.
Can any give me how to create a good Database design (one or more) with excellent performation. Right now I have one Database for Job Portal related tables excluding membership. The mapping of key fields including the fields that are enabled for Text indexing are given below.
(JobSeekerTable - Stores Personal Details) JobSeekerId (PK) ...............
(JobSeekerResumeTable - Stores Resume Details) JobSeekerResumeId (PK) JobSeekerId (FK) Job Title (enabled Text Indexing) ........
(JobSeekerDocTable - Stores Resume Details) JobSeekerDocId (PK) JobSeekerId (FK) Resume (as varbinary) (enabled Text Indexing) Covering Letter (Text) ........
(JobSeekerPostingTable - Stores Job Postings Saved by the Job Seeker) JobSeekerPostingId(PK) JobSeekerId (FK) JobPostingId (FK) ......
(JobSeekerAppliedTable - Stores Job Postings Applied by the Job Seeker) JobSeekerAppliedId(PK) JobSeekerId (FK) JobPostingId (FK) .....
(JobPostingTable - Stores the information of the Job Posting created by Employer) JobPostingId(PK) CompanyId(FK) Job Title (enabled Text Indexing) Job Desc(enabled Text Indexing) .....
(JobPostingConTable - Stores the information of the Job Posting Location Details ) JobPostingConId(PK) JobPostingId(FK) .....
Eventually more tables would be added. Can any one tell me how to speed up the performance (particulary search engine fo Employer for searching resumes & Jobseeker for searching job Postings.) I hope I have mentioned everything clearly.
I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.
I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:
Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.
One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?
I have below DB structure in MSSQL for a small application which follow relational approach. Data retrieval (for Hostels) will need several Join, may be Key-Value approach where data retrieval will be fast.
I have a problem in design the tables. My main task is to learn how to give the Match Score.
I have hundreds of dataset and one of them is like this:
Test Record Number: 19 Prospect ID = 254040088233400441105260031881009 Match Score = 95 Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11] Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1 Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1 Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11 Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11 Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11
Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:
and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:
a) prospect table as case table with id AS key, prospectID AS input & predictable;
b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .
But it shows error for having multiply key columns...
And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this?
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this
I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.
for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:
i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:
UPDATE acc1167 sum1 = sum amnt1 where ven1 = '1167' from accnt sum2 = sum amnt from accnt SET amnt = sum2/sum1*amnt FROM acc1167
I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it. CREATE TABLE SKU_DATA ( SKU Integer NOT NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
But it doesn't explicitly tell wherther Interpreted (disk-based) tables can be accessed by Natively compiled stored procedures.And SQL Server Express edition doesn't allow creating Memory-optimized objects to very this.
I need to copy all the data from all the tables in a database to a copy of this database on another server. What feature of SSIS should I take advantage of to accomplish this?
We have an SLA for 8am, most times the data warehousing jobs complete at 8:05am. Adding an additional process/set of tasks to this package would obviously make matters so I'm trying to update/copy/replicate the data in the fastest manner. Typically we're talking 2 marts (10-20GB) with 2 large tables (5-10 mill records) and 20 marts (0.5 - 5 GB) with many more smaller tables (~40 tables with record count ranging from 1 to a million)
Additionally please indicate if the design/feature you suggest can handle (pushing schema changes and additions to the target server) schema changes or new tablesviews added to the source database.
My only idea so far...is using the import wizard (in Management Studio) to create an SSIS package (top copy all the tables from one server to another) and saving it to the server, Then executing this package after the job is complete. However this would not work if the schema of a table changed, or if a a table is added. Moreover I don't think I can edit this package in visual studio.
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view ( Name: Product Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc Table 2: Is a lookup table that provides the names of columns Name: lookupTable Structure: tableName, ColumnName, columnValue Values: Product, Attribute1, Color Product, Attribute2, Size Product, Attribute3, Flavor Product, Attribute4, Shape
Hi All, I am designing database where few of the master tables will reside in different database or in case different server. Scenario is Server "A" with Database "A" may host the "Accounts" table. Server "B" with Database "B" may host the "Product" table. I am designing database "Project" which will hosted in Server "A". My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables. How do i design my database and sql queries? I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
Hello, I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..andwhat sort of service or products they can supply. For instance a Employment agency maybe under professional services Table 1 Customer Customer_ID = primary key,,,, Sector_ID = Foreign keyComapany Name, Address, Phone, Postcode etcTabel 2 Industry SectorsSector_ID = primary key,,,,Customer_ID= foreign key banking, Education,Prof Services, etc Table 3 Trading ActivityTrading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= FkEmployment Agent, School, Lawyer etcTable 4 ProductsProducts_ID = primary key,,,,Trading_ID = foreign keySupply frozen foods, transport services, sports goods, etc Table 5 Account Account_ID = primary key,,,,Customer_ID = foreign keyAccount Name, Credit Limit, Payment Terms, Open date, Account contact etc One big point of confusion is, can I have the Customer_ID from the principal Customers tablein every table as a foreign key or must the tables be chained together one after the other as such. Advice appreciatedThanks
Hi, I need a hand with designing a database. I am collecting results from a survey which has the following questions: Call ref? How did you place your support call? Were you satisfied with the amount of time you had to wait until getting acknowledgement of the support call placed? 1 = very satisfied and 10 = very unsatisfied. How happy were you with the customer service you received upon placing the support call? 1 = very unhappy and 10 = very happy.How satisfied were you with the amount of time you had to wait until you heard from an engineer? 1 = very satisfied and 10 = very unsatisfied. How satisfied were you with the time taken to get your problem/query resolved? 1 = very satisfied and 10 = very unsatisfied Did you feel the engineer had enough knowledge to deal with your call? 1 = very good and 10 = not very good Overall how satisfied were you with the support call placed? 1 = very satisfied and 10 = very unsatisfiedIs there anything we can do to improve the quality of the support and service you received? I want to store this in a database. Obviously I want to use best practice for design, normalisation etc. The stumbling block I am coming accross is the fact that each question has a number and each question has a score from 1 to 10 and storing this in the database. Any help appreciated! Thanks Andrew
I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided. My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments. What is best practice for putting this db structure together? Thanks Andrew
