normalization question
Date: Thu, 23 Apr 2009 09:53:25 -0700 (PDT)
Message-ID: <4d142477-3b5d-4e56-a7de-34415d2bb728_at_u10g2000vbd.googlegroups.com>
From: myotheraccount <levtoma_at_yahoo.com> Newsgroups: comp.databases.theory
Subject: normalization question
Date: Thu, 23 Apr 2009 09:53:25 -0700 (PDT) Organization: http://groups.google.com
Lines: 33
Message-ID: <4d142477-3b5d-4e56-a7de-34415d2bb728_at_u10g2000vbd.googlegroups.com> NNTP-Posting-Host: 209.48.164.194
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1240505605 18878 127.0.0.1 (23 Apr 2009 16:53:25 GMT) X-Complaints-To: groups-abuse_at_google.com NNTP-Posting-Date: Thu, 23 Apr 2009 16:53:25 +0000 (UTC) Complaints-To: groups-abuse_at_google.com Injection-Info: u10g2000vbd.googlegroups.com; posting-host 9.48.164.194;
posting-account=KxP0fAoAAAAOZwwOfkfCOwbMsXeXrN1D
User-Agent: G2/1.0
X-HTTP-UserAgent: Opera/10.00 (Windows NT 6.0; U; en) Presto/2.2.1,gzip(gfe),gzip(gfe)
Xref: textnews.cambrium.nl comp.databases.theory:31284
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 Thu Apr 23 2009 - 18:53:25 CEST