Re: Foreign Keys and 3NF

I agree with Zaaf. I would assume that CustomerID in LINE mean something different.

I find that this quite often means that Customer is not 3NF. Most of the systems I have worked have not normalized the address data. This causes customer via CustomerID to be used as an address reference for such things as shipping addresses, delivery address etc. The CustomerID and its foreign key in the line item table references the customer table to get an address.

A current case for me is where the Line structure identifies both the billing, shipping and other addresses for each line. Using customer as implied in the example above I saw when reverse engineering the structure the equivalent of customerID1 and customerID2. In fact I had to correct the structure to allow 0-n addresses associated with the line not just 0-2. At the same time there are also 0-n contacts for each line item such as " ATTN: Fred Smith" and/or "in case of spillage contact: J.P. Morgan" etc. These contacts do vary for each item on the order and I have seem live data with 4-5.

I aways recommend a generic and independant set of address structures since they do not come and go with customers, individuals or things.

30 years have taught me that customer structures are not simple or nice.

TomB

Zaaf <Arjan.Bos_at_icu.nl> wrote in message news:8dph9r$1i2g$1_at_buty.wanadoo.nl...
>
> Paul Keister <keister_at_dnai.com> wrote
>
> <snip>
>
> I would like to add the following to the discussion. If the datamodel is
 in
> 3NF depends indeed on the fact that the CustomerID is dependent on the
> OrderID. So ask yourself the following question. Is it really dependent,
 or
> could that CustomerID in LINE mean something different? What is the role
> that the relationship from CUSTOMER towards LINE is playing? It could play
> the role of adressee of the specific LINE item. Whereas the role the
> Customer is playing in Order could mean that he is accountable for the
> complete order.
>
> In other words, if the foreign key means the same as in the parent table,
> then it is a synonym and redundant. On the other hand, if it means
 something
> different, but just looks the same then it is a homonym and not redundant
 at
> all.
>
> Zaaf
>
>
Received on Tue Apr 25 2000 - 00:00:00 CEST