Re: Calculated value dilemma
- From: Tim X <timx@xxxxxxxxxxxxxxx>
- Date: Thu, 17 Jul 2008 19:06:16 +1000
Rainboy <mark.rainbow@xxxxxxxxx> 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
.
- Follow-Ups:
- Re: Calculated value dilemma
- From: Ed Prochak
- Re: Calculated value dilemma
- References:
- Calculated value dilemma
- From: Rainboy
- Calculated value dilemma
- Prev by Date: Re: Pivot / Crosstab With Count Unique data.
- Next by Date: Re: Pivot / Crosstab With Count Unique data.
- Previous by thread: Re: Calculated value dilemma
- Next by thread: Re: Calculated value dilemma
- Index(es):
Relevant Pages
|
