4 ms·
I've worked on a few large scale financial databases and none of the ones I've worked on use transactions (using Oracle). Most of them are insert only with bi-
by jasondc 13y ago
I've worked on a few large scale financial databases and none of the ones I've worked on use transactions (using Oracle). Most of them are insert only with bi-temporal data: http://en.wikipedia.org/wiki/Bitemporal_Modeling http://en.wikipedia.org/wiki/Bitemporal_Modeling
- lclarkmichalek 13y agoDo you have any more info on that technique? The wikipedia article is a little scarce.
- buro9 13y agoA simple entry into the field is to imagine that instead of storing a row in which you have a single balance, you would instead have an append-only table in which you store all transactions including their timestamp and auditing info. The balance would then be calculated as the sum of transactions, and you could modify the query to view a balance in the past by only including transactions before a certain point in time. A reversal or correction would be an append action, not a modification to existing data. Thus giving you an accurate history of the data. This is a simple entry point, as bitemporal modelling usually affects the entire datastore and also has to handle potentially massive data and so requires ideas of how to archive old data, and provide performant ways of knowing the current state. There are lots of tricks here, and the sum of those tricks is how bitemporal modelling is achieved.
- jes5199 13y agoI've seen the before-and-after at a company that didn't use this sort of modelling and then added it. It was like night and day - suddenly we stopped making embarrassing errors with people's money. Even without really optimizing it for speed, and just using MySQL, and without changing any of the other data model, it works.
- jasondc 13y agoInteresting, could you describe how the errors occurred?
- jes5199 13y agonot in detail, but imagine: you have code that walks a complex object graph every day to figure out how much money to move. The business rules interact in complicated ways. Do you trust that you'll always get the same value out of that system? Versus "select sum(value)" as a single query...
- jasondc 13y agoSure, this post helps describe the design: http://www.codeproject.com/Articles/17637/Bitemporal-Database-Table-Design-The-Basics http://www.codeproject.com/Articles/17637/Bitemporal-Databas...