Re: Foreign Keys and 3NF

Hi!

Paul Keister wrote:
>
> Although I've never had a formal education in database design, I've always had an
> intuitive understanding and appreciation of relational concepts. Studying the first
> three normal forms has helped me immensely in understand when it is - and is not - a
> good idea to break columns out in to a separate table.
>
> There is one construct that I have run into in other designer's data models that I
> don't quite understand. I've always felt intuitively that it is wrong, and it does
> not appear to be 3NF. 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. The most common argument for adding this column is to
> increase the efficiency of a customer - line item join. I've also heard it argued
> that this column is necessary where it is possible to have a null order record.
>
> 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?

AFAIK, it is not third normal form. David even pointed out that it is not second normal form (but I can't remember those definitions for sure).

As Karl said it is a question of performance. The problem about redundant data could easily be solved by using an update trigger on the customer number in the order table that also updates the corresponding line items.

One other thing to be considered is whether you should pull the customer name and address into the order table all together. This might become necessary, if you have to track the name and address that the order was shipped or billed to. If you have to have this kind of information, you can't get it by joining the customer table since the data of the customer might have changed since the order was placed. And this would not compromise the third normal form, since you possibly don't have the same data in customer and order tables.

Regards,
Heinz Received on Tue Apr 18 2000 - 00:00:00 CEST