Re: normalization question

>>

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 ...
Rule 3 states that items can be sent once from /each/ vendor to /each/ customer, not once from /any one/ vendor to /any one/ customer. This means that item1 could be sent in a box from vendor1 to customer1 and that item1 could be sent in a different box from vendor1 to customer2.

<<

I imagine a workflow like that: a customer orders item(s) (not twice) from a specific vendor, places an order for it, the order will be stuffed into boxes (it does not matter to the customer into which box it goes), then the box will be shipped to the customer.

Orders
{item, vendor, customer}
Primary key {item, vendor, customer}
If one item can just be sent once (not quite clear if so) then Item could be the primary key

Box_Stuffing
{box, item}
Primary key {box, item}
If one item can just be sent once (not quite clear if so) then add a UNIQUE constraint on Item

Shipments
{box, vendor, customer, shipment_date}
Primary key {box} -- if they are not re-usable boxes (unknown from the spec.)

It should be enforced by some means of the RDBMS that a box which is put into a shipment is not empty (view with check option or similar).

brgds

Philipp Post Received on Mon May 04 2009 - 16:53:46 CEST