Re: Calculated value dilemma

From: Tim X <timx_at_nospam.dev.null>
Date: Thu, 17 Jul 2008 19:06:16 +1000
Message-ID: <871w1syj5j.fsf_at_lion.rapttech.com.au>


Rainboy <mark.rainbow_at_gmail.com> writes:

> 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.
>

I think you may be a little off the mark with your interpretation of 3NF. Its not about not storing calculated values. Although a bit simplistic, think of it as not having the same data stored in multiple places because then you can get data inconsistencies when one copy is updated and the other is not - how do you determine which is correct?

Also, don't worry too much about efficiency initially. What your describing is normal expected application of the technology. You also don't seem to be dealing with that many records given common data set sizes and the processing power of hardware these days. Optimizing too early is often a fatal mistake. Wait and see (ideally in a testing stage rather than a production stage - you will be surprised how much performance can be affected by the right use of indexes. If performance becomes an issue and you cannot resolve it with available database features (partitioning, indexing, summary tables, views etc) then you can consider using summary tables and data archiving etc.

> 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...

Think about it from a different perspective. The problem isn't that the balances get all messed up. The problem is allowing historical data to be modified. If you cannot change the balance or date of a record and you cannot insert a record with a retrospective date, then your historical data doesn't change and hence any calculations based on that data doesn't change. Note that most accountants and auditors would not be very impressed with any system that allowed either updates of past records or insertion of records retrospectively.
>
> 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.
>

Personally, I'd avoid storing the balance unless the calculation of that balance is too resource hungry. As long as you can't change data retrospectively and can't insert records retrospectively, just calculate the balance when you need it.

What I would possibly do is -

  1. Use a timestamp on the records that is automatically set when you insert the record. i.e. not set by the user.
  2. If performance becomes an issue, consider having either some form of partitioning or end of month process. Track when the end of month process has been run and use that to generate a historical summary table. If/when you need a detailed report that covers more than the current period, use the data in the historical summary table.
  3. Take advantage of 300 years of modern accounting practices and design your system as a double entry accounting system. This provides additional checks and balances and you tend to get lots of other benefits, such as simplified auditing processes. Include the ability to enter bank statements or deposit receipts. This can be used to ensure everything balances. Don't allow a month to be closed off until everythinig balances.
  4. Ensure you have some type of journaling function that will enable adjustments. Data will be entered incorrectly - that is the one thing you can guarantee - for example, simple transposing of numbers during data entry etc. You need to be able to 'fix' such things and you need to be able to do it in an acceptable way from and accounting and auditing perspective. Rule of thumb, you should be able to reproduce *all* the data entry processes that have occured easily and pretty much transparently.
  5. If your database supports constraints (foreign keys, check constraints, not null, primary key constraints, etc then use them. In the short term, it may feel like a lot of extra work, but once you start coding the application logic, things become a lot easier. Much better to prevent bad data from getting in than trying to fix it once it does.
  6. Make sure you thoroughly know and understand how this information is recorded and managed now. I'm assuming it is a manual system. Being really familiar with current practice will provide valuable information in both the design of your underlying data model and the overall implementation. Don't try to just replicate the process using technology - your solution wil almost certainly require those who manage the process to refine their process, but make sure you understand their current process before trying to design them a new one.

Finally, make sure there isn't an affordable or open source solution that would suffice. Building such an application is a fun project and if its for an organisation you feel warrants such support, its generally a good thing. However, all applications need maintenance and support. If your not prepared or unable to provide this maintenance and support, they need to get it from somewhere else and it can come at a cost - particularly for one off custom apps. Given you have never designed and implemented a database and application before, you are almost certainly going to get things wrong. I've been working in the industry for over 20 years and I still get things wrong - the only difference is that I get it wrong less often and when I do it tends to be less critical.

good luck

Tim

-- 
tcross (at) rapttech dot com dot au
Received on Thu Jul 17 2008 - 11:06:16 CEST

Original text of this message