Re: Calculated value dilemma

From: JOG <jog_at_cs.nott.ac.uk>
Date: Thu, 17 Jul 2008 04:33:18 -0700 (PDT)
Message-ID: <81be038f-9b25-4c63-b461-9f04d8607daf_at_e39g2000hsf.googlegroups.com>


On Jul 16, 3:00 pm, Rainboy <mark.rain..._at_gmail.com> wrote:
> On Jul 16, 1:13 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
>
>
> > Rainboy wrote:
> > > Hi all
>
> > > I am designing a database for my charity (we are a local special needs
> > > charity) to replace our existing one, and my main goals are to make
> > > the database accurate, easy to use, and able to expand (the current
> > > database is not!).
>
> > > The database will hold contact and membership details for about 300
> > > families, staff/volunteers and other contacts, along with event
> > > booking (we hold about 5 a week) and payment details.
>
> > > So, for each family we need to see how much they owe, plus a full list
> > > of all their transactions. I am considering having a transaction
> > > table, which details every payment anyone has ever made.
>
> > > IDEA 1
> > > To follow normalisation rules I should avoid having calculated values
> > > in the table, and I should therefore simply calculate the balance
> > > whenever I need it by querying the table. I guess as the database
> > > grows I would have to periodically 'consolidate' the balance to
> > > maintain efficiency.
>
> > Where did you learn that was part of a normalization rule? It's just
> > plain wrong.
>
> > > However, I see each updated balance as a snapshot in time - when I
> > > look back to figure out 'who paid what when', I want to be 100%
> > > confident that the database will return the same balance it would have
> > > returned at the time. If a rogue entry were to somehow find its way
> > > into the table, it would mess all the balances up...
>
> > Historical data should accurately reflect the history. What if a
> > calculation changes? What if a calculation depends on the instantaneous
> > value of some attribute where no history is stored? As a hypothetical
> > situation, suppose families with 5 or more kids get a special discount.
> > When baby #5 is born, the family doesn't suddenly get a discount on all
> > their past transactions.
>
> > > IDEA 2
> > > As keeping historically accurate info is important, 'hard-coding' the
> > > balance in the table seems to me like it might be the most appropriate
> > > solution, even though it breaks the normalisation rules.
>
> > Exactly which normal form do you suppose it violates?
>
> > > Any suggestions would be greatly appreciated!
>
> > > Mark
>
> > > PS I've never designed a database before, so please let me know if
> > > I'm completely barking up the wrong tree!
>
> > I think you have been misinformed. Where are you getting your information?- Hide quoted text -
>
> > - Show quoted text -
>
> Hmmmm...
>
> Well, I had assumed that the table would be violating 3NF because the
> calculated value of the balance would be dependent on the transaction
> amount, which is not the primary key.

3NF is deprecated really. You should start at BCNF when normalizing. Regards, J.

>
> Ah, I think I'm starting to understand... is historical data treated
> differently because as soon as an entry is 'in the past', the balance
> (at that time) no longer depends on the transaction amount, and it
> does only depend on the primary key of transaction ID. So I just have
> a balance column, and whenever I want someone's current balance, I
> just query this historical table for the most recent entry? And I
> won't be breaking any rules? Smooth.
>
> Although (assuming I've got this right) something does intuitively
> bother me about this... can't quite put my finger on it! I feel a bit
> wary that the database's accuracy will depend on someone always
> entering in the correct balance to square with the latest transaction
> amount... what if they don't? I know it can be automated, but I don't
> feel that comfy knowing that the database structure doesn't take care
> of it, and it'll have to be some extra logic. Does this make sense?
>
> Mark
>
> PS Thanks for your reply. I hope you don't mind bearing with me
> while I get my head around these new concepts!
Received on Thu Jul 17 2008 - 13:33:18 CEST

Original text of this message