Re: Foreign Keys and 3NF

Paul Keister <keister_at_dnai.com> wrote

<snip>

> ... To illustrate this suspicious technique, let's take the
> canonical invoicing example of customer, order, and line item, where a 1
 to many
> relationship exists between customer and order, and between order and line
 item.
>
> Many designers will add a foreign key to the line item table that
 references the
> customer table. This does not seem to be 3NF, since the customer key
> is completely dependant on the order key.

<snip>

> What I would like to know is:
>
> Is this actually in third normal form, because foreign keys are special?
> If it isn't, is it an acceptable denormalization strategy?
>

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 Fri Apr 21 2000 - 00:00:00 CEST