Re: Foreign Keys and 3NF

The design is not 3NF, because it's not 2NF. As to whether the design is faulty or not, the answer is "it depends". That's the universal
answer to every question about whether a design is "correct".

Normally, in the situation you describe, the line item table has a compound key. It's made up
of the order number, and the line item number.

2NF would require that customer number, if included in the line item table, be functionally
dependant on the entire key. As you pointed out, it's not, since it's FD on the order number by itself.
So the answer is that including the customer number in the line item table is denormalizing.

When could this cause a problem? Well, if you ever entered an order with the wrong customer,
and wanted to go back and update the data about that order, you'd have to be careful to
update all the line items of the order in a single atomic transaction. Otherwise you could leave
the database in an inconsistent state.

You might find that the orders require more disk space, and that entering new orders take a few more disk IOs. These effects should be minor, and can be traded off against the performance advantages of joining customers and line items as opposed to joining customers, order headers, and line items.

If it were up to me, I'd start with the normalized design, and change the design if I found a performance drawback.

David Cressey

   david_at_dcressey.com

Paul Keister wrote in message <8ddq3d$7tp$1_at_bob.news.rcn.net>...
>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?
>
>I will appreciate any comments.
>
>Thanks,
>--
>Paul Keister
>
>http://www.dnai.com/~keister
>
>
Received on Mon Apr 17 2000 - 00:00:00 CEST