Database Design – Is It OK to Keep a Frequently Updated Value in a Table?

database-designrdbms

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?

Best Answer

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.

Related Solutions

Which option is better for not slow down the performance of queries

I would use a system where each payment is recorded separately

Typically, you'd want to keep the "balance" consistent over time and this requires them to be recorded individually.

the ability to link payments
record cancellations separately

I would never concatenate previous payments into one opaque string.

I also wouldn't worry about performance yet: this is premature optimisation. If you do expect 100s of millions of rows, there are other techniques to improved performance. Otherwise, concentrate on data integrity and correctness

Help with designing a multi relational schema / domain model

It might help if you also had some table descriptions in your question, but here goes...

A network has many blocks.

You could do this very simply with a network_id in each block. That way there is no restrictions on how many blocks can reference a network, but you ensure that each block is only in one network.

Each block belongs to a single client. A client may have many blocks in one network, and can also have many blocks in many networks.

Having a client_id in your block would ensure that each block can only reference one client, but a single client could be referenced by many blocks (and it won't matter which network they're in). So maybe your block table needs to look something like this:

block
-----
  ID
  client_id (FK to client.id)
  network_id (FK to network.id)
  [other fields...]

I also then need to be able To browse a client and query all his blocks which he has

The SQL for that could look something like:

select client.name, client.id, block.id
from block
inner join client
on client.id = block.client_id
where client.id = $SOME_PARAMETER_FOR_CLIENT_ID

Best Answer

Related Solutions

Which option is better for not slow down the performance of queries

Help with designing a multi relational schema / domain model

Related Question