Re: Foreign Keys and 3NF

Hello,
I wonder if the example described is not part of a surrogate key approach. DOM Heinz Huber wrote in message <38FBEE69.E4977A15_at_elbanet.co.at>...
>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