Re: The MySQL/PHP pair

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:aag962-efe.ln1_at_pluto.downsfam.net...
> "Bill H" <wphaskettatTHISISMUNGEDadvantosdotnet> wrote:
> Bill, I'm not sure we're using the same terms in the same ways.
>
> What you call "auditing" I call "automation" but they do the same thing,
> generate additional columns out of existing columns, and also update other
> tables such as order_header based on order_detail.
>
> But I don't believe this is 1NF we are violating, but rather 3NF.
Strictly
> speaking, the following is in 3NF (we'll assume unique on order# + item,
> which works in an example):
>
> Order # Item Qty
> ======= ====== ===
> 123 WIDGET 5
> 123 BWHIP 6
>
> As soon as I bring in price, I have technically violated 3NF because it is
> key-dependent upon Item, not Order + Item. This is common practice, and
> we'll assume that our table is now:
>
> Order # Item Qty Price
> ======= ====== === =====
> 123 WIDGET 5 5.00
> 123 BWHIP 6 7.00
>
> As a further Abomination to the priests of Normalization, I will add the
> column Extended = price * qty, which is a further violation of 3NF:
>
> Order # Item Qty Price Extended
> ======= ====== === ===== ========
> 123 WIDGET 5 5.00 30.00
> 123 BWHIP 6 7.00 42.00
>
> So my original question was about 1NF. You stated that 1NF will not
support
> auditable systems, but it seems to me that we will violate 3NF, not 1NF.
> Did I mistake your meaning?

I don't think this is really a normilization problem. Normalization really only applies to logical designs, and not to the physical level implemented in a product-specific schema where various implementation trade-offs must be made.

At the logical level, you have some functional dependencies implied by your auditing requirement. The price column most likely reflects a time-varying price, hence you would have a product pricing table with start/end dates or some such. But yet, at the physical level of the schema, you may recognize that there is no business requirement to retain price history except for the audit, hence merge the two together. This is a valid physical design decision.

In the case of your extended price, the implied functional dependency is the formula used to calculate it. Since you don't expect it to change, you don't actually encode the formula as data. In this case, a better physical design might be to use a view, but due to limitations in actual DBMS products, this might prove inconvenient or impractical. But also, given an audit requirement to record the formula so that the result is verifiable, is may prove easiest at the physical level to just store the result.

Dave Received on Thu Nov 11 2004 - 09:33:29 CET