Re: Foreign Keys and 3NF
I am studying a Business Information Technology degree, and am in my final
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
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.
Paul Keister <keister_at_dnai.com> wrote in message
> Although I've never had a formal education in database design, I've always
> intuitive understanding and appreciation of relational concepts. Studying
> 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
> canonical invoicing example of customer, order, and line item, where a 1
> relationship exists between customer and order, and between order and line
> Many designers will add a foreign key to the line item table that
> customer table. This does not seem to be 3NF, since the customer key is
> 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
> that this column is necessary where it is possible to have a null order
> 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.
> Paul Keister
Received on Mon Apr 17 2000 - 00:00:00 CEST