Calculated value dilemma
Date: Wed, 16 Jul 2008 03:57:04 -0700 (PDT)
Message-ID: <0c6b861b-47a7-4a37-b6e0-298f61ce8408_at_z66g2000hsc.googlegroups.com>
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
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...
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.
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!