On Jul 16, 6:00 am, Rainboy <mark.rain...@[EMAIL PROTECTED]
> 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 member****p 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.
>
> 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...
and screw up an audit, and so many other things. Have you discussed
some of the requirements with an accountant?
>
> IDEA 2
> As keeping historically accurate info is im****tant, '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.
Why put it in the transaction table? Why abandon normalization?
Consider creating a balance summary. Different entity. Make it a view.
If the performance of the view ever gets to be an issue, then make it
a materialized view. With the right indices there shouldn't be much of
a performance drag. I don't think you have the data volume.
>
> 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!
You are getting overly concerned about performance before you even get
the core functionality working. With decent normalization, you should
be able to optimize later, if need be.
HTH,
ed
|
