CODEWITHSUNDEEP
database-designrelational-database
David
David

Reputation: 5016

Database design to see transactions made to each value

I am creating some financial software. In such software it is essential not only to know the current value of fields, but also to know what the value of the field was earlier, who made the change and when, so one can go back in history to for example see who committed a crime.

My question is what the best practice for creating such a design is? The DBMS I use is PostgreSQL. Of course it will be the current values of each field that are accessed mostly. Also, only changes to values need to get recorded so making snapshot backups does not seem like the appropriate way to do this.

I tried searching for "journaling" and "transaction history", but came up without results.

Upvotes: 0

Views: 104

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29629

Martin Fowler describes the design of financial transaction systems in his book "Analysis Patterns" - this is about OO design more than database design, but worth reading.

The traditional way is to create a table representing a "ledger", along the following lines:

transactionID | AccountID | TransactionDate | TransactionValue | OtherData...
-------------------------------------------------------------------

The balance at any point in time is the sum of the "TransactionValue" up to that date.

It's true that such tables tend to grow very large - but databases like Postgres are very effective at managing large datasets. In practice, you can easily manage tens or hundreds of millions of records on modern hardware, as long as you can rely on a good indexing scheme.

If you do run into a performance limit, the common solution is to create summary entries - so you move all records up to a certain date into an archive table, and insert a summary record for that date. Depending on your data volumes, you might do this by decade, year, month, or even week.

Upvotes: 1

Related Questions