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 |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2011-04-21 : 09:46:20
|
| Here are my tables:Orders------OrderID PKCustomerID FK (not null)Items-----ItemID PKOrderID FK CustomerID FK (not null)Now, normally this would violate Normalization since the OrderID would determine the Customer ID so the CustomerID should not be in the Item table.But here is the situation: Customers provide us Items on a daily basis and then later we group these into an Order.Is there an alternate of doing this to avoid breaking normalization ruls? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-21 : 09:59:48
|
| SOunds like you get the items for a customer not attached to an order and The items are later allocated to an orderso you should havecustomercustomeriditemItemidcustomeridorderorderidCUstomeridorderitemorderiditemidThe order/item entries are filled in when the item is allocated to an orderThis could be customer, order, item depending on how you allocate orders.I'm not clear whether the items are received for/from a customer or whether they are independent - that would mean the items don't have a customer but come from the orderitem table.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-21 : 10:03:26
|
| My last job had a similar structure, we had to add uninvoiced items for billing purposes. The OrderID would be filled in during the next billing cycle. Technically it may break normalization rules, but I wouldn't consider it a stoning offense. It's simpler than the alternatives. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-04-21 : 10:09:13
|
| Thankyou everyone. |
|
|
|
|
|
|
Normalization Question