Re: normalization question

R(CustomerID, VendorID, ItemID, BoxID)

R is a key itself. So there are no non-prime attributes and R must enforce #1, #2, #3 and BCNF.

Simpler is better!
"myotheraccount" <levtoma_at_yahoo.com> wrote in message news:4d142477-3b5d-4e56-a7de-34415d2bb728_at_u10g2000vbd.googlegroups.com... I've run into a design issue that I just can't wrap my head around. I tried to model here below with different types of information, as the actual information I am modeling is confidential.

Here are the rules:
1) There are items, vendors, customers, and boxes. 2) Boxes contain items and are sent from one vendor to one customer on a specific date.
3) Items can only be sent once from each vendor to each customer

The question is, how is this modeled in the highest possible normal form.

So far I’ve come up with two possible solutions, both unsatisfactory: Box Entity, which has a unique key of CustomerID, VendorID, and Date BoxItem Entity, which has a unique key of BoxID, and ItemID

This solution does not enforces rule #3

Alternatively, I've come up with:
Shipment Entity, which has a unique key of CustomerID and VendorID. Box Entity, which has a unique key of ShipmentID and Date ShipmentItem Entity, which has a unique key of ShipmentID and ItemID, and contains a BoxID

This does not comply with third normal form. What happens when the ShipmentID/BoxID of a ShipmentItem does not match the ShipmentID of the Box? It could be argued that this does not adequately enforce rule #2

Any ideas on the “proper” way to model this? WWDACD (Would would Date and Codd do?)
Thanks in advance! Received on Mon Apr 27 2009 - 08:15:34 CEST