SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Jul 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    TABLE txns
    id txn_type from_id to_id user_balance
    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
    id  email
    11  email@site.com
    22  notheremail@site.com
    
    QUERY RESULT:
    id  balance email
    11  7.00    email@site.com
    22  10.00   notheremail@site.com
    Thanks for any help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    (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. #4
    SitePoint Member
    Join Date
    Jul 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'.
    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
    Code:
    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. #5
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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. #6
    SitePoint Member
    Join Date
    Jul 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    On the architecture front:
    In an account model, "balance" should never be a column in your database, it should be a calculated value.
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guiriverde View Post
    ...I thought querying balance would be quicker than having to sum/subtract the whole time.
    it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it is
    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. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    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.
    an awesome, and rare, observation

    i am a fan of transio
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    an awesome, and rare, observation

    i am a fan of transio
    Thanks, Rudy. Likewise!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •