Re: Calculated value dilemma

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? Received on Wed Jul 16 2008 - 14:13:21 CEST