Re: The MySQL/PHP pair
Date: Wed, 10 Nov 2004 08:05:45 -0500
Message-ID: <aag962-efe.ln1_at_pluto.downsfam.net>
"Bill H" <wphaskettatTHISISMUNGEDadvantosdotnet> wrote:
> Kenneth:
>
> "Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message
> news:ulf462-prv.ln1_at_pluto.downsfam.net...
>> Bill H wrote:
>>
>> >
>> > I think this is the crux of the debate. 1NF cannot properly model an
>> > audit
>> > system. It can model parts of it but not the fundamental requirement
>> > of it unless delusional assumptions are made (that nothing can go wrong
>> > and we can foresee everything).
>> >
>> > We know this. We make assumptions and adjustments to try to account
>> > for
>> > this. We joke about auditors and accountants. But it is, nonetheless,
>> > true!
>>
>> Well I didn't know this. Can you offer a simple example of how you have
> to
>> outside of 1NF to solve an audit situation?
>
> An audit system needs totals from two different directions. It needs to
> compare these totals with calculated totals to discern if there's a likely
> problem within the data. This is a pretty simple and effective solution
> to
> auditing needs. You've mentioned (I think it was you but apologize if it
> wasn't) the (quantity * price) equation. A simple 1NF model would hold
> both variables but not need the actual cost because it can be calculated.
>
> An audit model, on the other hand, would demand that the actual cost,
> calculated at the time of data creation, be stored along with the
> calculation components. In addition, it would be auditomically (I
> couldn't help myself) correct to hold the order total or all goods
> purchased within
> the order record, even though this amount could be calculated. This
> provides the auditor with much better assurance that nothing is missing.
> For instance, if individual detail records disappeared (for a variety of
> reasons) the totals crossfooting would most likely spot this.
>
> This is just the tip of the iceberg, so to speak.
>
> Bill
Bill, I'm not sure we're using the same terms in the same ways.
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?
-- Kenneth Downs Use first initial plus last name at last name plus literal "fam.net" to email meReceived on Wed Nov 10 2004 - 14:05:45 CET