# 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
> 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