Re: Calculated value dilemma

On Jul 16, 3:00 pm, Rainboy <mark.rain...@xxxxxxxxx> wrote:
On Jul 16, 1:13 pm, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> wrote:



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?- Hide quoted text -

- Show quoted text -

Hmmmm...

Well, I had assumed that the table would be violating 3NF because the
calculated value of the balance would be dependent on the transaction
amount, which is not the primary key.

3NF is deprecated really. You should start at BCNF when normalizing.
Regards, J.


Ah, I think I'm starting to understand... is historical data treated
differently because as soon as an entry is 'in the past', the balance
(at that time) no longer depends on the transaction amount, and it
does only depend on the primary key of transaction ID. So I just have
a balance column, and whenever I want someone's current balance, I
just query this historical table for the most recent entry? And I
won't be breaking any rules? Smooth.

Although (assuming I've got this right) something does intuitively
bother me about this... can't quite put my finger on it! I feel a bit
wary that the database's accuracy will depend on someone always
entering in the correct balance to square with the latest transaction
amount... what if they don't? I know it can be automated, but I don't
feel that comfy knowing that the database structure doesn't take care
of it, and it'll have to be some extra logic. Does this make sense?

Mark

PS Thanks for your reply. I hope you don't mind bearing with me
while I get my head around these new concepts!

.

Relevant Pages

  • Re: Calculated value dilemma
    ... the database accurate, easy to use, and able to expand (the current ... I am considering having a transaction ... and I should therefore simply calculate the balance ... aggregate and the values that combine to form the aggregate is not due to a ...
    (comp.databases.theory)
  • Re: Calculated value dilemma
    ... the database accurate, easy to use, and able to expand (the current ... and I should therefore simply calculate the balance ... Historical data should accurately reflect the history. ... calculated value of the balance would be dependent on the transaction ...
    (comp.databases.theory)
  • Re: interest calculation
    ... I also wouldn't recommend computing interest from 'transaction to ... I use one record if the balance or the interest rate changes. ... the history like a invoice table to the line item table. ... records will bear the serial number of the closing record. ...
    (comp.databases.filemaker)
  • Re: Calculated value dilemma
    ... I am designing a database for my charity (we are a local special needs ... and I should therefore simply calculate the balance ... Historical data should accurately reflect the history. ...
    (comp.databases.theory)
  • Re: computational model of transactions
    ... the rows that have been changed in the transaction log. ... varieties of the SNAPSHOT isolation that Microsoft faithfully, ... the same consistent database state. ... At time T4 the $100 transaction completes, recording a balance of $450. ...
    (comp.databases.theory)