CODEWITHSUNDEEP
mysql
Kayode
Kayode

Reputation: 11

Database data inconsistency

I have three tables on my database the BILL,RECHARGE AND SENT.The BILL table has a column named UNITBALS and the RECHARGE table has a column named UNITS also the SENT table has a column named VOLUME.What normally should happen is that when a customer recharge his account his new UNITS will be sum with his old UNITS on the RECHARGE table and the sum of the VOLUME of the total sent message on the SENT table will be subtracted from the sum of the UNITS on RECHARGE table and the result will be used to update the UNITBALS on the BILL table.But the problem now is that when customer recharge their account the UNITBALS on the BILL table is updated with the new balance or sometimes the UNITBALS is being updated with higher values than it ought to be.There is a kind of inconsistency in my database now and the customers are complaining.

Upvotes: 0

Views: 205

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52655

So from what I gather you are storing the results of a aggregate calculation on BILL.UNITBALS

Specifically

BILL.UNITBALS = SUM(RECHARGE.UNITS) - SUM(SENT.VOLUME)

Unless you made a serious error, what's likely going on is due to a pending transaction and the transaction isolation level you're missing a value that you should be taken into account.

Because of this issue I try and do whatever I can to avoid storing the results of a calculation in the DB

Typically this means that I do the calculation every time I retrieve the data. This may (or may not be) a performance issue but at least you get more opportunities to get the correct information.

If the perf penalty is too high then I recommend that you do a reconcilation only once every 24 hours and add a disclaimer like "payments made will not appear on your account for X business days"

Upvotes: 1

Related Questions