Mysql Query problem

Hi, I hope someone can offer some advice for the following query example. Or … can you point me to a good source for simple model. I am now hearing things like ‘double entry ledgers’ and wondering if there was a much simpler way to do all this.

So, the balance for a user (after each transaction) is contained in the user_balance column. And the current balance is contained in the last txn for that user.

TABLE txns
[U]id[/U] [U]txn_type[/U] [U]from_id[/U] [U]to_id[/U] [U]user_balance[/U]
1  in         0      11    10.00
2  out        11     0     9.00
3  out        11     0     8.00
4  out        11     0     7.00
5  in         0      22    10.00

TABLE users
[U]id[/U]  [U]email[/U]
11  [email][/email]
22  [email][/email]

[U]id[/U]  [U]balance[/U] [U]email[/U]
11  7.00    [email][/email]
22  10.00   [email][/email]

Thanks for any help.

you seem to be relying on the id values to determine sequence – that’s probably going to bite you some day when you have to post older rows

add a datetime column

(I will agree with rudy that a datetime column would be helpful, considering the future, but for the purposes of this specific query, it’s moot, as the order of events doesnt matter to a flat total. If you need to make sure the person never went into negative balance, for example, datetimes are a must.)

That said. This is a… confusing table structure.
What is the purpose of ‘type’? Surely it’s redundant; if FROM = you, money went out from you, if TO = you, money went in to you.
What does the type field hold if user 11 gives money to user 22?

Two ways to change it:
Table 1:
Type = In/Out, ID field.
(ID = party involved, Type is whether to add or subtract from balance).
Cons to this table: Just because User 11 sent out 10 dollars, and User22 received 10 dollars, does not mean you can say user 11 paid user 22.

Table 2:
No type field, 2 ID fields (From/To)
(ID = 0 = ‘unspecified user’)
Cons to this table: Possibly harder to construct a balance query due to having to do conditional statements.

My bad, actually I do have a datetime in there, should have put it in the example. If you could just imagine datetime as a column called ‘date’. :slight_smile:
The problem is the tos and froms are not all the same type of object. They reference entries in different tables, so there will be duplicate ids and thus I can’t simply SELECT … WHERE id=to_id

I reckon I over-simplified my example, maybe this will help

id date type    from    to amount giver_balance receiver_balance
1   001 deposit  NULL   11  10.00      10.00       NULL
2   002 txn1       11  333   1.00      09.00       1.00
3   003 txn1       11  344   2.00      07.00       2.00
4   004 deposit  NULL   11  10.00      17.00       NULL
4   004 payout   NULL  334   1.00       NULL       1.00

So the transactions are:

  1. Deposit - Givers load money
  2. txn - Givers give money to receivers
  3. payout - Receivers take money

At the time (a long time ago) I needed a quick solution and it seemed perfectly logical. Now I’m trying to pull answers out such as daily totals etc, I’m not so sure.

On the architecture front:

In an account model, “balance” should never be a column in your database, it should be a calculated value.

Store transaction amounts, and sum them per user_id to get your balance.

Something like this:

to_account      from_account    trans_type      amount          created
----------      ------------    ----------      ------          -------
2               1               deposit         100.00          2011-01-01
1               2               transfer         50.00          2011-01-01

You can then use creative SQL to get a running total in a view for something similar to what you’re presenting above.


Ok, I wish I’d known that … being niaive I thought querying balance would be quicker than having to sum/subtract the whole time.


it is :slight_smile:

Yes, quicker, but quicker is not always better. It’s not proper modeling of the data being stored. A balance is not an element of data. It’s calculated, and therefore should never be persisted in your DB.

It also presents a dimension of potential conflict. If, for example, a ledger item was missed… from last month… inputting that record today requires that you recalculate all ledger entries’ “balance” records that were previously stored, and run a subsequent process to verify that all balances have been properly adjusted. If you’ve ever used a financial application, you know this is not an uncommon occurrence.

It’s just a mess waiting to happen, with a lot of potential pitfalls.

If speed is a requirement in your financial application, e.g. for reporting purposes, you should have a separate reporting database that “flattens” your data periodically.

If you have less than 10 million records in your table, discussions of performance are really moot, though…

an awesome, and rare, observation

i am a fan of transio :slight_smile:

Thanks, Rudy. Likewise! :smiley: