Re: The MySQL/PHP pair

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Wed, 10 Nov 2004 08:05:45 -0500
Message-ID: <aag962-efe.ln1@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.

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?

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

Received on Wed Nov 10 2004 - 07:05:45 CST