- the balance is recorded when the amount changes are recorded in the serial meter in the database. It can not only realize your function, but also facilitate audit
- if you don't move the database, find out all the details first, and then calculate each corresponding balance according to the type.
other unexpected, or can be a combination of two, according to the date segment to record the balance, query as long as a small amount of water can be calculated, feel that there is no need
I also have a total balance field in another table, and it feels a little awkward to record another balance field here in detail.
how do you all do it?
for example, if I update another balance table field, do I have to update the balance in this detail?
user transaction schedule
< table >
< th > user id < / th >
< th > time < / th >
< th > transaction type < / th >
< th > Action type < / th >
< th > variable amount < / th >
< th > current balance < / th >
< tr >
< td > 1 < / td >
< td > 2018-03-09 < / td >
< td > recharge < / td >
< td > income < / td >
< td > 300 < / td >
< td > 300 < / td >
< / tr >
< tr >
< td > 1 < / td >
< td > 2018-03-10 < / td >
< td > Buy < / td >
< td > Expenditure < / td >
< td > 30 < / td >
< td > 270 < / td >
< / tr >
< tr >
< td > 1 < / td >
< td > 2019-03-11 < / td >
< td > recharge < / td >
< td > income < / td >
< td > 50 < / td >
< td > 330 < / td >
< / tr >
< tr >
< td > 1 < / td >
< td > 2019-03-12 < / td >
< td > sell < / td >
< td > income < / td >
< td > 50 < / td >
< td > 380 < / td >
< / tr >
< tr >
< td > 1 < / td >
< td > 2019-03-13 < / td >
< td > transfer < / td >
< td > Expenditure < / td >
< td > 200 < / td >
< td > 180 < / td >
< / tr >
< / table >
I have another table that records the balance, which has a 1-to-1 relationship with the user basic information table.
user balance table
< table >
< th > user id < / th >
< th > balance < / th >
< tr >
< td > 1 < / td >
< td > 180 < / td >
< / tr >
< tr >
< td > 2 < / td >
< td > 200 < / td >
< / tr >
< tr >
< td > 3 < / td >
< td > 100 < / td >
< / tr >
< / table >
is there a problem with my design? Both tables have balances.
for example, I am going to do an operation now, the user id=1 transfers money-100, and gives the user id=2.
then I will first go to the user's balance table, deduct 100 from the user's id=1, and update it into a new balance.
then, I'm going to insert a transaction record in the transaction schedule, id=1, transfer type, expenditure, 100,
calculate the current balance again.
now there are two ways to calculate,
one is to update the current balance field of the transaction schedule with the balance of id=1 in the user's balance table, minus 100.
another way is to go to the transaction list to find the current balance value of a transaction detail, and then deduct 100 to update the new transaction detail.
what's the problem? How should I do it? How to update the current balance in this new transaction detail.
at the same time, I also want to operate id=2 users, he wants to add 100, the above operation, and do it again.
if you feel so complicated and out of control, you will make mistakes.
is there something wrong with my design? How do you all do it?
this needs to maintain the balance fields of the two tables, and there can be no errors. The advantage is that the inquiry is fast? No complex sql statement statistics?
if you don't add this current balance, use SQL statements to automatically calculate it and return it to the php result set, which is inefficient?
how to choose?
I think it would be better to record the current balance while recording each transaction.
Let's talk about your situation:
the calculation formula is:
balance of a transaction = current balance-(total recharge amount between a transaction point in time and now) + (sum of consumption records between a transaction point in time and current point in time)
the above is only when the action type is simple to add and to decrease .
in this way, the efficiency is still too slow when counting from time to time. It is recommended that you still need to add a field of transaction balance.
< hr >
the book follows the above, big brother, you are too tangled.
in fact, if you choose between the two situations, you will know which one is fast and direct.
when you have consumption (recharge or expense), calculate the current balance, take your balance, and update your user table balance.
then use your balance and insert it directly into the consumption record.
it's much easier to maintain than you count all the time.
