Re: Foreign Keys and 3NF

From: Robert Francis <robert_at_kita.co.uk>
Date: 2000/04/17
Message-ID: <38fb2889_at_eeyore.callnetuk.com>#1/1


I am studying a Business Information Technology degree, and am in my final year.
The example you described is third normal form. The example you have given is called 'Transitive Dependency".
Book terminology now - a condition where A, B and C are attributes of a relation such that if A = B and B = C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C) I do not see why you think the customer key is completely dependant on the order key, surely it is the other way around

Customer                        =            Order
=                Line item
customer number                           Customer number
Customer number
                                                     Order number
Order number

Line number

As the line item contains the customer number, select statements are much easier to write as you can simple picked all the items a customer has ordered from the customer number instead of having to link up three tables to get the same information.

Hope this helps, I know it is third normal form but it is a lot harder explaining why it is.

Robert

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

Original text of this message