I'm trying to find the correct design in the following situation :
My customers have prepaid accounts, and of course I want to record all movements on their account (account operations).
My first and foremost need is to be able to send an account log to my customers every period, so technically, I would need only the amount and description of the operation. But as a matter of consistency and security, I want to be able to reference each operation to whatever action it was generated from.
An account operation can either be a billing operation (generated through the recording of sales), a credit operation (generated through the recording of account crediting), or a special operation (with a description field that can be filled accordingly).
Here are my questions:
- since I'm going to have a history of the changes to the account, is it bad practice to have a field to hold the current account balance in the account table, or should I recalculate the balance from the history of operations every time I need it?
- the tables for billing/credit/special operations have in common the date of the operation, amount, and account ID, which are in fact all that are needed to define an account operation and build an account log. So do I need a table at all to record my account history? For instance, shouldn't all account operation have a unique number accross the different types, for other reference purposes in the database? In that case I would build an "account_history" table, but how would I reference the operations in this table? My "reference_id" would be variable, sometimes referring to the "bill_record" table, sometimes referring to the "credit_record" table, or the "special_record" table. What's the correct solution?