Re: Normalization and Derived Information

Kenneth Downs wrote:

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

I assume you mean qty * 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.

Third normal form. If there exists functional dependencies of the form A-->B where A and B are non-key attributes, then your table is not in 3NF. Here you would have a dependency of the form (Qty, Price) --> ExtendedPrice.

The solution (from a pure normalization standpoint) would be to not add ExtendedPrice at all, as it can always be derived from Qty and Price. (For an example of an anomaly that can be created with the non-3NF data, imagine what happens when you change Qty in the first row from 5 to 10, but don't update ExtendedPrice accordingly.)

That being said, theoretically you would not add the Extended Price, and instead do the multiplication on the fly when you query the table. From a practical standpoint, you might put in in there anyway and deal with the possibility for anomalous data. Your call -- the rules aren't set in stone. Perhaps the best use for normalization is as a set of general guidelines, and as a basis for understanding what and how things can go wrong as a result of your design decisions.

Larry Coon
University of California Received on Wed Sep 22 2004 - 23:21:40 CEST