| Author |
Topic |
|
Jillsy
Starting Member
20 Posts |
 Posted - 2006-01-23 : 09:17:01
|
| HelloBefore I start just wanna say thanks for all the top advice so far - much appreciated. My current quandry is this:-I have a table called STOCK with columns ITEM_ID, ITEM & PRICE. Most of the stock is my own produce but i do buy-in some items. What i want to do is add some extra columns to STOCK for the supplier details on those items i buy in. The problem is that a lot of the fields in the supplier details columns will be empty. Would this be a problem or would it be better to have a seperate table for supplier details? Jill |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-23 : 09:35:14
|
| I would say that the best would be a seperate table because you can potentially have more than one supplier, and the infomation for each could be different. This would require a one to many relationship. Even if it is not currently the case, who knows about the future?CODO ERGO SUM |
 |
|
|
Jillsy
Starting Member
20 Posts |
Posted - 2006-01-23 : 09:44:08
|
| OK, lets say hypertheticaly that i was only ever going to have one supplier for one item then would you still have a seperate table? What i'm really interested in is if it's deemed bad practise to have a table with lots of empty cells, if so why? By the way my stock changes all the time so none of it is ever permanent and therefore neither are my suppliers. So if i erase an item the supplier goes with it if i have one table, this is not a problem for me.Jill |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-23 : 09:44:45
|
| Except in extreme cases, I do not favor the idea of creating secondary tables with one-to-one relationships to a primary table. The overhead on null columns is not large, so consolidate your data. |
|
|
|
Anup Shah
Starting Member
14 Posts |
Posted - 2006-01-24 : 16:58:55
|
| lets consider this setuation,as u said, table is going to change all the time that means there will be lot insert-delete-update operation. So this is the asswer of your second qustion. because when u have write dominant table having large number of null values it costs a lot to Database compiler and optimizer. it will increase your execution time. u can see this differance for 100 records and 1000 records.besides that it will afeect more when u have more than 2 join and of course this all depend on size of the table too.Now if your table not gonaa to change a lot, and mainly u r going to perform read operation on it then it is good to keep data in one table as we do with our master table. it will give u faster access to data because of easy avability. |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2006-01-24 : 17:18:51
|
You should first think about your design logically. Then think about it physically. From a logical perspective, you are storing stock information. Everything in this entity should help to describe the stock item. I am assuming that item_id is used to identify a row in stock. The rest of the data in this entity should provide more information about that specific item_id. The supplier details do not describe an item_id. They describe a supplier. Logically, supplier should be a separate entity. You would then migrate the supplier_id of supplier into the stock entity. From a physical perspective, there would be a unique index on supplier_id on supplier. You would also create an index on supplier_id on stock. (It may or may not be a unique index.) Join performance should be fine. But, during your testing, if it is not, that is when I would consider denormalizing, and collapsing the two tables. Good luck.quote:
Originally posted by Jillsy
HelloBefore I start just wanna say thanks for all the top advice so far - much appreciated. My current quandry is this:-I have a table called STOCK with columns ITEM_ID, ITEM & PRICE. Most of the stock is my own produce but i do buy-in some items. What i want to do is add some extra columns to STOCK for the supplier details on those items i buy in. The problem is that a lot of the fields in the supplier details columns will be empty. Would this be a problem or would it be better to have a seperate table for supplier details? Jill
|
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-01-24 : 18:14:44
|
quote:
Except in extreme cases, I do not favor the idea of creating secondary tables with one-to-one relationships to a primary table. The overhead on null columns is not large, so consolidate your data.
"Consolidate"!?!?!? That's newspeak for denormalise.Back to the posters question.You need 3 tables with the "link" table (StockSupplier) having 2 candidate keys.Stock (StockID PK)Supplier (SupplierID PK)StockSupplier (StockID, SupplierID, PK (StockID, SupplierID) && PK (StockID))DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-24 : 23:40:20
|
quote:
Originally posted by byrmol"Consolidate"!?!?!? That's newspeak for denormalise.
Not when consolidating one-to-one relationships.By the way byrmol, from your sig-line, I'd bet you'd get a kick out of this thread:http://www.dbforums.com/showthread.php?t=1208350 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-01-25 : 00:07:58
|
| >>I'd bet you'd get a kick out of this thread: http://www.dbforums.com/showthread.php?t=1208350I am not that horrified. At least they are not trying to shove it down childrens' throat.Raelians 1, Fundamentalist Christians 0.DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-25 : 03:25:35
|
| My 2p worthMake a new SUPPLIER table. Plan to store the supplier's Name, Address, Telephone number and typically I also see stuff [rightly or wrongly, but it might give you an idea or two!] like Date of first purchase, Date of last order, Total purchase value in last N months and so on. Each supplier should have an ID - either let SQL allocate the next number sequentially, or you allocate a code (we tend to use first 4 letters of the name, and a 2digit tie-break) [enforce All Caps for any alpha-numeric code]Store the Supplier ID on the Stock record.Or store it in a Product Code, Supplier "link" table as byrmol said (which I prefer (PITA if you need to move to this one-product-available-from-many-suppliers model in the future, costs nothing to implement it now).I would also put a CreateDate and UpdateDate on every [within reason!] table.You probably should add an index on the STOCK table for the SupplierID (or the "link" table if you go down that route).Note that the Primary Index on the link table should be on Product Code then Supplier ID [in that order] as Product Code is likely to be more selective given that you plan to only have one supplier per product.Note that you do NOT need to have a row in the LINK table for every product - you only need a row for products that have external suppliers. This may raise some problems in its own right - e.g. the need to join STOCK to the LINK table using an outer Join.Kristen |
|
|
|
|
extra columns or another table?