Preserve historical data.

Question

I am writing a standard database-backed business application. Assume that I work with bank accounts as well as "Decisions". A decision is a choice the user takes of moving money from one account to another. Each decision is taken at a particular date. A decision can have one or more "source accounts", and each "source account" will have one or more "destination accounts". We call them source accounts because money will leave the account... Also each decision and account have related information such as names, balance, etc.

The user would like to view his past decisions, and has an unusual business requirement. By default, when decision time comes (for example the first of each month), he would like all previous decisions to be copied over to the new month. This is because he's very likely to take the same decision over and over,but he would like the flexibility of changing any parameter of a decision for a new month, without impacting his previous months.

As a programmer, when the user starts a new month, I simply insert new rows in my Decision table, but I update Decision.Date. That way, the user can change the list of source accounts for each decision, each month. Is there a more elegant way of achieving the same thing, without copying over all decisions?

Answer 1

You could do a copy on write instead. Just show last month's "Decisions", and let the user pick and edit them. But, when it's "saved", you'd insert a new row instead of updating. That makes last month's "Decisions" more of a template for this months rather than an absolute.

The choice between the two would probably weigh pretty heavily on what you do if the user doesn't want last month's "Decisions" for this month. Are you deleting the extra rows? Do they automatically take effect with no user input, or are they deactivated when created, etc.

Another option could be a versioning mechanism. Each "Decision" gets an Id and a version (or Effective Date). Each month's Decision has both the DecisionId and the DecisionVersion. Updating "key fields" of the Decision results in a new version being created. That keeps a history of changes and a lineage for each "Decision".

Versioning would make sense to me in a scenario like "Automatic Savings Plan". You'd create a "Decision" with a name "Automatic Savings Plan", and you'd be able to change the amount, source accounts, etc. - but they'd still be part of the "Automatic Savings Plan" Decision.

Answer 2

Your "Decisions" are considered transactions in Accounting, subdivided into T accounts when a transaction has multiple debit/credit sources.

I would continue to log account transactions - it's an audit trail for both the developer and the client. But there is a concern with this table filling up for limited (if any use) - the most likely search is going to be within the current month, going back ~6 months. You'd want to archive the data based on a rolling date rather then at the year change.

Most accounting systems I've worked with allow for the ability to schedule outgoing transactions (IE: mortgage, car loan, medical, insurance) - there's no harm in inserting new records based on the previous information, updating the date accordingly.