Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc.

Up until now we had a Card entity which has a collection of Account entity, and each Account has an Amount, which updates in every Deposit/Withdrawl.

There is a debate now in the team; someone has told us that this breaks Codd's 12 Rules and that updating its value on each payment is trouble.

Is this really a problem?

If it is, How can we fix this?

share|improve this question
3  
There is an extensive technical discussion on this topic here on DBA.SE: Writing a simple bank schema –  Nick Chammas Jan 29 '13 at 16:28
1  
Which of Codd's rules did your team cite here? The rules were his attempt to define a relational system, and did not mention normalization explicitly. Codd did discuss normalization in his book The relational model for database management. –  Iain Elder Jan 29 '13 at 18:28

5 Answers 5

up vote 28 down vote accepted

Yes, that's non-normalized, but occasionally non-normalized designs win out for performance reasons.

However, I would probably approach it a little differently, for safety reasons. (Disclaimer: I don't currently, nor have I ever worked in the financial sector. I'm just throwing this out there.)

Have a table for posted balances on the cards. This would have a row inserted for each account, indicating the posted balance at the close of each period (day, week, month, or whatever is appropriate). Index this table by account number and date.

Use another table for holding pending transactions, which are inserted on the fly. At the close of each period, run a routine that adds the unposted transactions to the last closing balance of the account to calculate the new balance. Either mark the pending transactions as posted, or look at the dates to determine what's still pending.

This way, you have a means of calculating a card balance on demand, without having to sum up all the account history, and by putting the balance recalculation in a dedicated posting routine, you can ensure that the transaction safety of this recalculation is limited to a single place (and also limit security on the balance table so only the posting routine can write to it).

Then just keep as much historic data as necessitated by auditing, customer service, and performance requirements.

share|improve this answer
1  
Just two quick notes. First that's a very good description of the log-aggregate-snapshot approach I was suggesting above, and perhaps clearer than I was. (Upvoted you). Secondly, I suspect you are using the term "posted" somewhat strangely here, to mean "part of the closing balance." In financial terms, posted usually means "showing up in the current ledger balance" and so it seemed like it was worth explaining that so it didn't cause confusion. –  Chris Travers Jan 29 '13 at 14:15
    
Yeah, there are probably a lot of subtleties I'm missing out on. I'm just referring to how transactions appear to be "posted" to my checking account at close of business, and the balance updated accordingly. But I'm not an accountant; I just work with several of them. –  db2 Jan 29 '13 at 14:32
    
This may also be a requirement for SOX or the like in the future, I don't know exactly what sort of micro-transaction requirements you have to log, but I would defo ask someone who knows what the reporting requirements are for later. –  jcolebrand Jan 29 '13 at 19:06
    
I would be inclined to keep perpetual data for e.g. balance at the start of every year, so that the "totals" snapshot never gets overwritten--the list simply gets appended to (even if the system stayed in use long enough for each account to accumulate 1,000 yearly totals [VERY optimistic], that would hardly be unmanageable). Keeping many yearly totals would allow auditing code to confirm that the transactions between recent years had the proper effects on totals [individual transactions might be purged after 5 years, but would be well-vetted by then]. –  supercat Jan 30 '13 at 14:14

On the other side, there is an issue that we run into frequently in accounting software. Paraphrased:

Do I really need to aggregate ten years of data to find out how much money is in the checking account?

The answer of course is no you don't. There are a few approaches here. One is storing the calculated value. I don't recommend this approach because software bugs which cause incorrect values are very hard to track down and so I would avoid this approach.

A better way to do it is what I call the log-snapshot-aggregate approach. In this approach our payments and uses are inserts and we never update these values. Periodically we aggregate the data over a period of time and insert a calculated snapshot record which represents data at the time the snapshot became valid (usually a period of time before present).

Now this does not break Codd's rules because over time the snapshots may be less than perfectly dependent on the inserted payment/usage data. If we have working snapshots we can decide to purge 10 year old data without affecting our ability to compute current balances on demand.

share|improve this answer
1  
I can store calculated running totals, and I am perfectly safe - trusted constraints ensure my numbers are always correct: sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/… –  AlexKuznetsov Jan 29 '13 at 14:19
    
I think log-aggregate-snapshot is simpler and leads to fewer edge cases (for example, forgetting to update the running total, or a trigger being disabled that might do that, or something). If you want to go that route, go for it. Also doesn't your schema allow for an inventory item to have more than one NULL previous quantity or does SQL Server treat NULLs as a distinct value for the purpose of a unique constraint? –  Chris Travers Jan 29 '13 at 14:23
1  
There are no edge cases in my solution - a trusted constraint will not let you forget anything. I do not see any practical need for NULL quantities in a real life system that needs to know running totals - these to things contradict each other. If you do see a practical need, please share your sceanrio. –  AlexKuznetsov Jan 29 '13 at 14:34
    
I understood your question better - you can have only one NULL previous quantity, so this is not a loophole. –  AlexKuznetsov Jan 29 '13 at 14:51
    
Ok, but then this is not going to work as is on db's which allow multiple NULLs without violating uniqueness, right? Also your guarantee goes bad if you purge past data, right? –  Chris Travers Jan 30 '13 at 1:29

For performance reasons, in most cases we must store current balance - otherwise calculating it on the fly may eventually become prohibitively slow.

We do store precalculated running totals in our system. To guarantee that numbers are always correct, we use constraints. The following solution has been copied from my blog. It describes an inventory, which is essentially the same problem:

Calculating running totals is notoriously slow, whether you do it with a cursor or with a triangular join. It is very tempting to denormalize, to store running totals in a column, especially if you select it frequently. However, as usual when you denormalize, you need to guarantee the integrity of your denormalized data. Fortunately, you can guarantee the integrity of running totals with constraints – as long as all your constraints are trusted, all your running totals are correct. Also this way you can easily ensure that the current balance (running totals) is never negative - enforcing by other methods can also be very slow. The following script demonstrates the technique.

CREATE TABLE Data.Inventory(InventoryID INT NOT NULL IDENTITY,
  ItemID INT NOT NULL,
  ChangeDate DATETIME NOT NULL,
  ChangeQty INT NOT NULL,
  TotalQty INT NOT NULL,
  PreviousChangeDate DATETIME NULL,
  PreviousTotalQty INT NULL,
  CONSTRAINT PK_Inventory PRIMARY KEY(ItemID, ChangeDate),
  CONSTRAINT UNQ_Inventory UNIQUE(ItemID, ChangeDate, TotalQty),
  CONSTRAINT UNQ_Inventory_Previous_Columns UNIQUE(ItemID, PreviousChangeDate, PreviousTotalQty),
  CONSTRAINT FK_Inventory_Self FOREIGN KEY(ItemID, PreviousChangeDate, PreviousTotalQty)
    REFERENCES Data.Inventory(ItemID, ChangeDate, TotalQty),
  CONSTRAINT CHK_Inventory_Valid_TotalQty CHECK(TotalQty >= 0 AND (TotalQty = COALESCE(PreviousTotalQty, 0) + ChangeQty)),
  CONSTRAINT CHK_Inventory_Valid_Dates_Sequence CHECK(PreviousChangeDate < ChangeDate),
  CONSTRAINT CHK_Inventory_Valid_Previous_Columns CHECK((PreviousChangeDate IS NULL AND PreviousTotalQty IS NULL)
            OR (PreviousChangeDate IS NOT NULL AND PreviousTotalQty IS NOT NULL))
);
GO
-- beginning of inventory for item 1
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
VALUES(1, '20090101', 10, 10, NULL, NULL);
-- cannot begin the inventory for the second time for the same item 1
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
VALUES(1, '20090102', 10, 10, NULL, NULL);

Msg 2627, Level 14, State 1, Line 10
Violation of UNIQUE KEY constraint 'UNQ_Inventory_Previous_Columns'. Cannot insert duplicate key in object 'Data.Inventory'.
The statement has been terminated.

-- add more
DECLARE @ChangeQty INT;
SET @ChangeQty = 5;
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
SELECT TOP 1 ItemID, '20090103', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

SET @ChangeQty = 3;
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
SELECT TOP 1 ItemID, '20090104', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

SET @ChangeQty = -4;
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
SELECT TOP 1 ItemID, '20090105', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

-- try to violate chronological order

SET @ChangeQty = 5;
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
SELECT TOP 1 ItemID, '20081231', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "CHK_Inventory_Valid_Dates_Sequence". The conflict occurred in database "Test", table "Data.Inventory".
The statement has been terminated.


SELECT ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty
FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate              ChangeQty   TotalQty    PreviousChangeDate      PreviousTotalQty
----------------------- ----------- ----------- ----------------------- -----
2009-01-01 00:00:00.000 10          10          NULL                    NULL
2009-01-03 00:00:00.000 5           15          2009-01-01 00:00:00.000 10
2009-01-04 00:00:00.000 3           18          2009-01-03 00:00:00.000 15
2009-01-05 00:00:00.000 -4          14          2009-01-04 00:00:00.000 18


-- try to change a single row, all updates must fail
UPDATE Data.Inventory SET ChangeQty = ChangeQty + 2 WHERE InventoryID = 3;
UPDATE Data.Inventory SET TotalQty = TotalQty + 2 WHERE InventoryID = 3;
-- try to delete not the last row, all deletes must fail
DELETE FROM Data.Inventory WHERE InventoryID = 1;
DELETE FROM Data.Inventory WHERE InventoryID = 3;

-- the right way to update

DECLARE @IncreaseQty INT;
SET @IncreaseQty = 2;
UPDATE Data.Inventory SET ChangeQty = ChangeQty + CASE WHEN ItemID = 1 AND ChangeDate = '20090103' THEN @IncreaseQty ELSE 0 END,
  TotalQty = TotalQty + @IncreaseQty,
  PreviousTotalQty = PreviousTotalQty + CASE WHEN ItemID = 1 AND ChangeDate = '20090103' THEN 0 ELSE @IncreaseQty END
WHERE ItemID = 1 AND ChangeDate >= '20090103';

SELECT ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty
FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate              ChangeQty   TotalQty    PreviousChangeDate      PreviousTotalQty
----------------------- ----------- ----------- ----------------------- ----------------
2009-01-01 00:00:00.000 10          10          NULL                    NULL
2009-01-03 00:00:00.000 7           17          2009-01-01 00:00:00.000 10
2009-01-04 00:00:00.000 3           20          2009-01-03 00:00:00.000 17
2009-01-05 00:00:00.000 -4          16          2009-01-04 00:00:00.000 20
share|improve this answer
    
It occurs to me that one of the huge limits of your approach is that calculating the balance of an account on a specific historical date still requires aggregating, unless you also make the assumption that all transactions are entered sequentially by date (which is usually a bad assumption). –  Chris Travers Feb 2 at 1:39
    
@ChrisTravers all running totals are always up-to-date, for all historic dates. Constraints guarantee that. So no aggregating is needed for any historic dates. If we have to update some historic row, or insert something back-dated, we update all the later rows' running totals. I think this is much easier in postgreSql, because it has deferred constraints. –  AlexKuznetsov Feb 2 at 1:47

This is a very good question.

Assuming that you have a transactions table that stores each debit/credit, there is nothing wrong with your design. In fact, I have worked with prepaid telco systems that have worked in exactly this way.

The main thing you need to do is ensure that you're doing a SELECT ... FOR UPDATE of the balance while you INSERT the debit/credit. This will guarantee the correct balance if something goes wrong (because the entire transaction will be rolled back).

As others have pointed out, you'll need a snapshot of balances at specific periods of time to verify that all of the transactions in a given period sum with the period start/end balances correctly. Write a batch job that runs at midnight at the period end (month/week/day) to do this.

share|improve this answer

The balance is a computed amount based on certain business rules, so yes you do not want to keep the balance but rather compute it from the transactions on the card and therefore the account.

You want to keep track of all transactions on the card for auditing and statement reporting, and even data from different systems later on.

Bottom line - compute any values that need to be computed as and when you need to

share|improve this answer
    
even if there may be 1000s of transactions? So i'll need to recompute it every time? can't it be a little hard on performance? can you add a little bit about why this is such a problem? –  Mithir Jan 29 '13 at 13:42
2  
@Mithir Because it goes against most of the rules of accounting, and it makes issues impossible to trace. If you just update a running total, how do you know which adjustments have been applied? Did that invoice get credited once or twice? Did we already deduct the payment amount? If you track transactions you know the answers, if you track a total you do not. –  JNK Jan 29 '13 at 13:58
3  
The reference to Codd's rules is it breaks normal form. Assuming you track the transactions SOMEWHERE (which you will have to I think), and you have a separate running total, which is correct if they disagree? You need a single version of the truth. Don't fix the performance issue until/unless it actually exists. –  JNK Jan 29 '13 at 13:59
    
@JNK the way it is now - we do keep transactions and a total, so everything you mentioned can be tracked perfectly if needed, the Balance total is just to prevent us from recalculating the amount every action. –  Mithir Jan 29 '13 at 14:01
2  
Now, it won't break Codd's rules if old data may only be retained for, say 5 years, right? The balance at that point is not merely the sum of the existing records, but also the previously existing records since purged, or am i missing something? It seems to me it would only break Codd's rules if we assume infinite data retention, which is unlikely. This being said for reasons I say below, I think storing a continuously updating value is asking for trouble. –  Chris Travers Jan 29 '13 at 14:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.