Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tammy2512
Starting Member
8 Posts |
 Posted - 2010-07-15 : 14:12:02
|
| Hello,I am a fresher, and joined my current job a month back.My company is an auditing and a very small company. I am involved in designing and developing a database. I have some questions related to db design. Data comes from many different sources, so I am having a problem figuring out the primary keys for each entity.We have the following tablesclient-----auto generated Primary key(clientid)vendor----auto generated Primary key(vendorid)clientvendor---composite primary key(clientid, vendorid)employee----composite primary key(clientid and employeeid) or again an auto generated Primary keyinvoice----composite primary key(vendorid and invnumber) or again an an auto generated Primary key.purchaseorder----composite primary key(clientid and ponumber)or again an auto generated Primary keyproduct-----productid(autogenerated primary key)1. My question is whether to use a composite key or a surrogate key; in terms of performance of data retrieval.We would have approximately 50,000 records in each entity and growing day by day. 2. I am not sure till which form should I normalize, for example product details repeats in purchase order, invoice and productmaster. I thought of just using the product master table for product details. But the business requires product details to repeat in purchase order and invoice for comparison purposes to catch potential fraud.This rule is resulting in data redundancy. So i am not sure whether these tables need to be further normalized. 3. Data for some of the entities such as client and vendor comes from an online form. Do these entities require normalization?Please do bear with me; as I am a fresher and my questions may sound silly. Thanx in advance for the help. |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2010-07-15 : 15:07:25
|
| I think best thing for you is to read couple of chapters from a good book or a good website and follow the instructions properly. Once you have questions then ask them here... sorry if I sound rude. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-15 : 18:39:46
|
For all those tables, except clientvendor, I would use an IDENTITY as the PK.I would have an index on the composite - e.g. employee PK/clustered index = employeeID, secondary index on clientid (no need to include employeeid as its part of the PK and will thus be part of the index key anyway) [although watch out if you have few Clients and logts of Employees that the index may not actually be used as the clientid won't be very selective - i.e. SQL may favour a table scan instead of using the index when youa re looking for all employees for clientid=1234Foreign Key between employee.clientid and client.clientidproduct.productid - products tend to have a Product Code already .... might be better to use that if one exists, and it doesn't change. If it is liable to change then use an IDENTITY ID for PK and create a secondary index on productCode"product details repeats in purchase order, invoice and productmaster"It shouldn#t be repeated. Store the ProductID on P/O and Invoice. I would store the Price in the P/O / Invoice (so that you have a record of what the price was at the time), but I would not store Product Name, Units etc. in P/O or Invoice - I would only store that in Product table. Most people are happy to use the "current name" even if it has changed but if there is a need to know what the product name was at the time of Order / Invoice then that's a bit more tricky (but I still wouldn't store it in the P/O / Invoice record), if you need to know what the Product Name etc was at the time of Order / Invoice then I would NOT store the Price either and handle all of those using a history table of "what the Price, Name, Units were at the time of Order / Invoice""the business requires product details to repeat in purchase order and invoice for comparison purposes to catch potential fraud."I'm sceptical that that catches fraud. I would just put a trigger on the Product table and store an audit/history of all changes. Store the "version number" of the product record on the Order / Invoice if you like = much less redundancy than storing the whole name etc."3. Data for some of the entities such as client and vendor comes from an online form. Do these entities require normalization?"Seeing as you are asking me  then YES.Client / Vendor State, Country, etc. should be normalised using some sort of lookup-tableWhat other columns have you got that you are concerned about?Name, Address, Phone number ... they are just columns in those tables. Unless you have multiple addresses (HQ, Branch1, Branch2 ... ??)You've already got Employees separate. Is there anything else that is, or might be, 1:many ? |
|
|
|
|
|
|
|
|
Primary key and normalization