Re: Foreign Keys and 3NF
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 numberOrder 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