DB Design For Performance
Jan 14, 2006
I develop my database with SQL Server 2005 Express.
I have a member profile, stored in tblMember.
In this profile I have a field "Religion" and a field "Country".
I COULD store the value of these fields just in those fields, eg Christian in the Religion field and "Sweden" in the Country field.
OR
I could make a seperate table for each, eg for tblReligion:
ReligionID (PK)
ReligionName (string)
and then in the tblMember make a FK to the PK of this table.
but I have many of these kind of fields (where a FK might be needed) in my tblMember.
So if I want to search in my members, I will first have to obtain the PK of the other tables.
My questions:
is this good DB design?
Will I get performance problems when I do it like this?
Are there better solutions?
Thanks ahead!!
View 3 Replies
ADVERTISEMENT
Jul 25, 2005
I have created an internal “e-mail� type system in our business application. The idea was pretty basic have a message table which would hold the data such as To, FROM, blah blah. Then there would be an Inbox table which would have the User Name and the Message.ID from the message table. By joining these two you would have the person’s inbox. Each table has an ID column which is the clustered index, The Inbox table has an additional Index on the User Name. These tables are very busy and large, here are the stats.
Messages – 860,000 rows est. Total
Inbox – 2,584,000 rows est. Total
For June there were 144,000 Messages of which 780 went to multiple recipients causing 276,000 Inbox references.
Everything works except getting a persons Inbox can get very slow. To address this we are currently Index Defragging and Re-Indexing. Now these action appear to be correcting the problem but only for a short time which is why we are doing it several times a day.
Solution?
I think the problem is that my most used Index (User Name in the Inbox) is not clustered? And I should have a larger Fill Factor than 90%?
Any advice greatly appreciated, the transaction logs are killing me.
View 4 Replies
View Related
Aug 10, 2005
Hi,Can anyone put a name to the following type of design...I need to be able to modify the amount/names of data "fields" storedfor an entity so instead of representing the data stored for an entityin fields associated with that entity you would relate it to an anotherentity which would contain a datafield type field and its associatedvalue, so different rows in the parent table could possibly havedifferent datafields associated with it.E.gThe parent table is "person" and it holds common data amongst allpeople - it has primary key of personID.There is another table assoicated with person called "personDetails".This would have fields "personID", "dataType" and "dataValue". Itmight have records like the following:personID, dataType, dataValue1,phone,1234561,email,t...@test.com1,state,NSW2,phone,9874563212,state,VIC3,phone,789456123Of course this is just an example - the parent entity could beanything.I've seen a few XML files implement this type of design. e.g<job><jobNumber>XYZ12345</jobNumber*><jobCreated>12-03-2005</jobCre*ated><jobdeatils><jobdetail type="WEIGHT">167</jobdetail><jobdetail type="WEIGHTMEASURE">KGS</jobd*etail><jobdetail type="CUBIC">45</jobdetail><jobdetail type="CUBICMEASURE">M3</jobdet*ail><jobdetail type="NUMPIECES">345</jobdetai*l></jobdetails></job>Hope someone can put a name to this sort of design. I want to researchinto this to see how people search effectively in this design and itsperformance compared to traditional methods etc etc.I would be implementing this design in MS SQL and it would be accessedby an 400-500 users via ASP.NET.Any help would be appreciated. Thanks Adam.
View 2 Replies
View Related
Apr 22, 2008
I have an opportunity to rebuild a database model with the express purpose of improving query performance. So given the following I have a few questions.
Table A (~500M records)
Primary Key Field (int)
Field 1 (varchar)
Field 2 (varchar)
Field 3 (varchar)
Field 4 (varchar)
Field 5 (varchar)
Table B (1B+ records)
Primary Key Field (int)
Foreign Key Field (int)
Field 1 (varchar)
Field 2 (varchar)
Field 3 (varchar)
Field 4 (varchar)
Field 5 (varchar)
* Assumed: Tables are inner joined on all queries. The database is readonly.
-- Most of my lookups are based on querying Field 1 of Table A. The data content of Field 1, Table A is 90% unique.
1) Would it be more beneficial to put the clustered index on Field 1 instead of the PK field in Table A?
2) Can an Identity column be non-clustered?
3) Alternatively, would it be beneficial to build a separate lookup table with just the PK & Field 1 of Table A, with a clustered index on the lookup table Field 1 which I join on Table A? (did that make sense?)
-- I have a secondary lookup that performs queries on Fields 1, 2, 3, 4 & 5 of Table B
1) Would it be more beneficial to create an additional indexed lookup column of the concantenated values of Fields 1-5 of Table B versus a covering index of all 5 columns?
2) Does a clustered index have to be unique?
3) Would a clustered index be more beneficial over Fields 1-5 or the special lookup column versus the PK or FK fields?
4) Would creating a special lookup table with just the requisite fields be more beneficial?
An extra question. The existing data model uses the CHAR datatype for all columns less than 9 characters wide and the columns are set to allow nulls. This requires every select statement to COALESCE() and RTRIM() all these columns. I intend to make all (affected) columns VARCHAR, NOT NULL with a default value of a 0-length string.
Will this enhance query performance?
Thanks in advance for any insight.
View 7 Replies
View Related
Jul 28, 2006
Hi
I have a database that stores all the data in one table that is only the data we are required to store hence it didnt require any other tables
the table has five colunms
Orderid, ordercost, orderdate, ordersystem, orderref
it is used in an web application that seraches for all the rest of the information using an orderid and displays the rest of the details the size of the table currently is about 123000 records that is increasing by 20000 every week.
i have an archive of the same data that has 7,666,000 records that are also going to be placed into the same database.
my question is is that a good idea of doing this?
are there any performance issues that i need to be aware of as currently the application runs quite quick?
View 15 Replies
View Related
May 13, 2015
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
View 2 Replies
View Related
Feb 13, 2002
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:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
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!
View 2 Replies
View Related
Aug 18, 2015
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  : Â
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos Â
| CTN=8 Nos
View 3 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jun 22, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies
View Related
Nov 7, 2003
Hi all.
I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)
I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)
My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)
1. When should I use IDENTITY field ?
2. If I do NOT - how can I lock a record when I add a new one
View 17 Replies
View Related
Jan 13, 2004
Hi,
I have 7 web forms wizard. after the user edit the fields, I need to keep a history log of the changed data only.
I am thinking of the following structure
log_id, user_id, field_id, old_value, new_value, transaction
Where field_id is a number indicates the changed field.
for example 1> First Name, 2> Last Name etc
Where transaction is ADD, DELETE, EDIT
Also i need to keep history of the details
for example an enterprise has 10 branches.
State, employees
NY, 1000
MD, 500
My log table will look like this
log_id, user_id, field_id, old_value, new_value, transaction
1, 1, 1, , Mick ,John , EDIT
2, 1, 3, ,NY , HI , EDIT
3, 1, 3, ,MD , , DELETE
4, 1, 4, ,500 , , DELETE
5, 1, 3, , ,NV , ADD
6, 1, 4, , ,700 , ADD
Is there a better way to log changes?
Thanks
View 1 Replies
View Related
Jan 16, 2004
I'm going to create a hardware/software inventory program to keep track on all the computers at the office. This program should be able to search the database using queries like "all computers with a cpu faster than 500Mhz".
I want some tips on my database design.
One solution is to create columns for each piece of hardware, i.e. cpu, ram, hdd etc etc. Then just run simple SELECT queries against them. The problem is that a computer may have many HDDs/CDs etc, and also other type of equipment may be entered in the database like switches and routers.
Another solution is to specify the valuetype + value in one table having a relation to another table containing the actual machines/routers/switches. The problem here is that I can't do numeric comparisons this way since "11" is less than "2", ("all computers with a cpu faster than 500Mhz").
Any suggestions?
View 3 Replies
View Related
Feb 10, 2004
Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]
Program A creates the records for Table C. In the process it reads from Table A and Table B.
Program B reads record from Table C. Program B now needs [SalesmanEmail] field.
The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?
View 5 Replies
View Related
Oct 15, 2004
hello,
i am working on a project with another guy as well. well, we have a table called lets say student and student_unit where student_unit holds the unit names the student is doing. and i was planning to use the same student_unit table to hold the result of that unit for that student as it is the case of adding one field but this guy who happens to be my boss thinks that result should be separated. but is it a better idea to make a separate table just for that result which is totally dependent on unit and student? that doesn't make any sense to me.
if asked why, he says for performance, modularity and object oriented approach and blah blah and honestly i think he knows nothing. so i would like u guys to tell him if there is really a need to create a separate table for attributes which is totally dependant on the same PK? having five more attributes on the same table hurts than creating a new table?
View 1 Replies
View Related
Oct 27, 2007
I think I might have dug myself in a hole here, so could use some advice on database design.
Currently, I have a table that records meeting minutes. The design is:
Code:
minutesid | int | 4 | no nulls
minutescat | varchar | 255 | allow nulls
minutesnotes | text | 16 | allow nulls
What this does is set up a minutes category, such as "Members Present", and the notes for that category, such as "Mrs. Peacock, Mr. Green, Professor Plum, Colonel Mustard, Miss Scarlett". Each individual category has it's own ID.
I need to develop an archive of each of the minutes. So if there are 15 categories and associated notes for the 15 OCT 2007 meeting, how can I design the archive table to include all of those categories and notes into one record for 15 OCT 2007, then for 15 NOV 2007 and so on ...?
Hope that makes sense. Thanks in advance ...
View 5 Replies
View Related
Sep 21, 2007
I have a table to store members. For each member, I have flags like manager, verified, etc. How should I be storing the flags? As individual columns in the same table and using bit datatype or creating a new table called MemberStatus and creating 1 row for each flag?
Thanx in advance.
Premal.
View 14 Replies
View Related
Mar 26, 2008
Hi,
I need to design a database, but i i just don know if i am doing it correctly.
Basically, i have an equipment which is sending me a lot of gauges readings (high limit, low limit, current level), electrical readings, hydraulic readings, alarms levels such as Fuel level, temperature level, etc etc, about 200 different types of readings, continuously. I may have different types of equipments and although most of the data are the same, some data types and its availability may be different too., depending on the type of equipment.
I want to keep this readings in a database, and this database will be updated constantly.
Should i create all of this different gauges readings in one big single table? If i do so, i only have one long row of data then for a particular equipment.If i put in different table, i can't see how i can create relationship among my tables with such kind of data.
Can you help on the possible ways of putting this equipment real time readings into DB?
View 3 Replies
View Related
Mar 12, 2004
I am trying to create a database that is used to create/store estimates for a manufacturing company. There are many things to consider in this estimate but I will isolate this question to the takeoff itself:
There are several categories in which costs are estimated and they are as follows : Product(s), Site Work, Transportation (Shipping), Field Materials, etc...
Should each of the above have their own "Takeoff" table, or would using one table and a gategory table be a better way to go?
Mike B
View 2 Replies
View Related
Jun 18, 2008
The data that I have is the following:
Car Manufacturer Factory CarCode
---------------- ------- -------
Ford Houston F-Hou
Ford Reno R-Hou
Chevy Houston C-Hou
Chevy Las Vegas C-LV
Honda SLC H-SLC
Ford SanFran F-SF
Chevy Miami C-Mia
I have a database design implemented already, but I'm receiving some disagreement about it from a co-worker. The way I see it is you have a car manufacturer and a factory location that are both unique, so I created a lookup table for each. I then created a branch table for the many-to-many relationship that exists between Manufacturer and Factory, and within that branch table I placed a column for CarCode, because it seems to me that Manufacturer and Location both determine the CarCode.
I also have an autonumber field within the branch table, which I then use for foreign key relationships.
Is there anything wrong with my design here?
Thank you in advance for your help!
View 5 Replies
View Related
May 1, 2007
I've fought with this issue for a week now and I can't come up with a simple way of doing it.
I have a database, with the following tables:
Members
MainCategories
Categories
Answers
The idea is that Members will log into the site, select the Categories from the MainCategories, fill out some data, which is stored in the Answers table. The problem is, each of the main categories have different fields. So a Main Category might be "Shoes" and the fields would be "Color,Size,Brand", and another Main Category might be "Cars" and the fields "Make,Model,Year,Color,Type,IsNew".
I thought about creating the Answers table with all the possible answers, linking it to the categories and members table, so we can view which members filled out answers for which categories.
Can anyone help me come up with a solution for this?
View 4 Replies
View Related
May 13, 2007
Please ignore my earlier post...
Please help me in designing the tables...
I used to get flat files which i need to import into a tables..
The flat files data which contains.....
FlatFiledata:
------------
100,1,xx,yy,,20030101
100,2,20030101
100,3,01,20030101
Below are the tables which i design please correct me..
Table1ForRow1
--------------
col Identity,
col1,100
col2,1
col3,xx
col4,yy
col5,,,
col6,20030101
Table1ForRow2
-------------
col Identity,
col1,100
col2,2
col3,20030101
Table1ForRow3
------------
col Identity,
col1,100
col2,3
col3,01
col4,20030101
When i queried then I want the output:
100,1,xx,yy,20030101,2,3,01
View 1 Replies
View Related
Jul 23, 2005
Hello all,I am trying to correctly model the relationship between products andversions within my db. The problem I have is how to store the versiondata. I need to collect the following information:1) Major Build Number (int)2) Minor Build Number (int)3) Build Number (int)4) SP number (int)What is the "correct" schema for this? How many tables and whatcolumns?(On a side note if any one has any links to useful examples of DBdesign could they post them?)Thanks,Jose
View 1 Replies
View Related
Oct 30, 2007
I inherited a SQL 2005 database from my bro-in law that has tables with names in the spaces and spaces and other characters in the column names. I link to these tables in an MDB file. Access 2003 sees this fine, but Access 2007 returns odbc--call failed errors. Is there anything I can do short of removing the spaces and characters (? %) from all of the table and column names? Is there a switch I can throw that will tell Access 2007 to ignore this and work like a previous version?
Thanks.
View 5 Replies
View Related
Jul 30, 2007
Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.
Raj
View 3 Replies
View Related
Sep 2, 2006
Hope this is in the right forum....Assume a DB design which has 3 entities Customers, Vendors, and Email-addresses. Customers and Vendors can have zero, one, or many Email-addresses. To normalize the many-to-many, I have an intersecting entity called Email-addresses-usage. I know how to handle the intersecting entity if it were only Customers and Email-addresses, i'm not sure about adding Vendors to the mix.My questions are:1) in sql managment studio, do I have a FK relationship from Customers to Email-addresses-usage and also a FK relationship from Vendors to Email-addresses-usage?2) If zero email addresses are allowed, how to I define a zero possibility from a relationship standpoint - i.e., do I just specify NOT enforing the FK constraint? but then, if email-addresses are present, I'd want the contraint to be enforced - how is this have my cake-and-eat-too accomplished?3) any tips/suggestions on how one might present this to an end user for data entry.Thanks
View 4 Replies
View Related
Oct 20, 2006
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
View 1 Replies
View Related
Jan 12, 2007
I'm creating a DB to track clients, programs, and client participation in the programs. They are service programs. A client can be in more than one program and a program can have more than one client.
Can someone give me an example of how they would layout the tables?
My guess is:
tblClient, ClientID
tblClientProgramLog, ProLogID, ClientID
tblProgramDetails, ProDetailID, ProLogID
tblPrograms, ProgramID, ProDetailID
I appreciate any suggestions,
View 4 Replies
View Related
Jan 15, 2007
What would the best / most correct way be to implement a relationship where you have for example a customer table, a partner table and a orders table and both customers and partners can have orders associated with them. This is just an easy way for me to describe the relationship I am looking at and is not really the data sets I am working with.
Following this analogy I currently have customers and orders and the orders table has a column customer_id to link each order to a customer. I now want partners to start to be able to place orders. It does not seem logical to me to have a second order table for them but the two identity columns that are the id columns would be on separate tables and thus could conflict. Only thing I can think of is to start the partner id identity column at a really high number. Is this the right thing to do it somehow does not feel right.
View 2 Replies
View Related
Feb 14, 2007
I have been given a DB that currently stores Organizations. I need to add the ability to track multiple contacts for the Organizations. The DB has several one-to-many relationships on the Organizations table, such as Documents and Notes. I would like to track these by contact as well as the organization.
Is it possible to add a tblcontact and a one-to-many relationships to the Organizations table as well as to the tblDocuments and tblNotes?
I appreciate any suggestions.
tblOrg (OrgID)
tblDocuments (DocID, OrgID)
tblNotes (NoteID, OrgID)
tblContact ???
View 3 Replies
View Related
Apr 10, 2007
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
View 9 Replies
View Related
Apr 26, 2007
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
View 1 Replies
View Related
