SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict fattyjules's Avatar
    Join Date
    Dec 2005
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to incorporate 'balance remaining' into database schema

    Imagine a system that has registered users, and those users 'top up' their account (by credit card or some other means). Then, as they consume the system's service, their 'balance remaining' is reduced by the appropriate amount.

    What's the best way to store the 'balance remaining' in the database? I see two ways of doing it;

    Method 1: Store all financial transactions in a table. To calculate the user's balance, simply sum all of the transactions for that user (e.g. +10.00, -2.50, -2.50 = 5.00). Pros: all financial information is recorded only once. Cons: calculating the balance could become resource intensive once the transaction database becomes large.

    Method 2: Store all financial transactions in a table, but also store the balance once against the user. The balance would have to be recalculated every time a transaction is entered. Pros: easy to get the balance. Cons: would have to check to make sure the balance matches what the transactions add up to.

    Any techinques I've missed? Any advice?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think you've covered the options nicely

    i'd use method 2

    if you're a user submitting a transaction, you are already (or should be) accustomed to a slightly slower response time, because you're submitting a transaction, and updates just take longer, everybody knows that

    so taking a couple more milliseconds to update one more table is not going to be noticed, since it's only that one user

    method 1 suffers from the flaw that if lots of people look at their balance, the overall system performance suffers, and everybody is affected
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •