Re: Foreign Keys and 3NF

The process you discribe is common enough to have a name - denormalisation.

The basi process is identify data items, normalise, bench test or build, denormalise to the minimum degree that meets performance criteria. The rational being that in some cases joining a pair of tables will degrade performance more than is acceptable versus an acceptable cost of maintaining redundant data.

The 'null order record' though is an odd rational as far as I can see. The order table largely maps to the order header (customer name, date, etc) and the line table to the line items. I would question how a line item could be ordered and not have an order date and/or not have a customer that ordered it. (Of course we don't physically put the customer name in the order<header> table but it is logically there because of the foreign key to customers that we do put in there).

I personally consider reasoned denormalisation for performance to be quite acceptable and part of the design process but am wary of denormalisation to handle 'odd' situations. I believe that often the situation is not odd but that the design is faulty.

My 10 cents worth
Karl

Paul Keister <keister_at_dnai.com> wrote in message news: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