Normalization and Derived Information

Here is the example. Consider the simplest order detail table, which contains the foreign key order #, a quantity column, and an item code:

Order # Qty Item
========= ==== ========

1000-23    5    XJ7
1000-23    5    ES300
1001-95    1    BUGWHIP
1001-95    3    FEEDBUCK

Now I want to add price to this table. If price were forever fixed in time, then my understanding is that this would be a violation of 3NF, because price is a transitive dependency upon Item. However, we normally consider price to be something agreed upon by both parties at the time of the order, and so it is actually dependent upon the order #, so our table now looks like:

Order # Qty Item Price
========= ==== ======== =========

1000-23    5    XJ7           75000
1000-23    5    ES300         42500
1001-95    1    BUGWHIP           2
1001-95    3    FEEDBUCK         10

That out of the way, now I add the column "Extended Price", a derived quantity that is calculated as Item * Price. The howls of protest begin, "that's not normalized!" If this is a normalization violation, I cannot figure out what form I've violated because there is no table where I can put this, it is dependent upon non-key variables within the table, but belongs to the table. FWIW, I feel intuitively that it is not normalized, but as I said I am not sure what form is violated.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me