Mysql Query problem

Databases
1

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@site.com[/email]
22  [email]notheremail@site.com[/email]

QUERY RESULT:
[U]id[/U]  [U]balance[/U] [U]email[/U]
11  7.00    [email]email@site.com[/email]
22  10.00   [email]notheremail@site.com[/email]

Thanks for any help.

2

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

3

(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.

4

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.

5

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.

Cheers

6

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

Cheers
Doug

7

it is :slight_smile:

8

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…

9

an awesome, and rare, observation

i am a fan of transio :slight_smile:

10

Thanks, Rudy. Likewise! :smiley: