homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

How do you model user account table

5+ Year Member

Msg#: 4524703 posted 1:10 am on Dec 4, 2012 (gmt 0)


We have a users table that has userID as a key and all the other user information. I need to create a useraccount table that will track the debits/advance deposits and credits/sales transactions performed by the user. This solution needs to be scalable. i.e. both the number of users as well as number of transactions per users are likely to be very high. We should be able to quickly able to find the current balance of a user to accept or decline a sales transaction.

I don't need an actual table definition, but need some pointers.




WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

Msg#: 4524703 posted 1:39 am on Dec 4, 2012 (gmt 0)

A table with
- UserID
- TransactionDate
- TransactionAmount
should give you what you need.
With an index on UserID, a query should give return the current balance for a given within a few seconds, even with millions of users generating thousands of transactions each.

You could also have an additional table with
- UserID
- CurrentBalance
that gets updated each time a transaction is made.

In other words, the first table would be a log of all the entries, and the second would be a running total for each user.

brotherhood of LAN

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

Msg#: 4524703 posted 2:04 am on Dec 4, 2012 (gmt 0)

Are you using MySQL?

Version >=5.6 has some nice improvements regarding partitioning.

When using InnoDB you will want to use something like an auto-increment field for transactions (or perhaps userid & a timestamp in a partitioned table) as big tables with random inserts can get fragmented quickly and slows down SELECTs

The advantage of partitioning is you can specify particular partitions on all your SQL statements & reducing a lot of overhead on larger tables. If suitable for your spec, you can remove older partitions/transactions and put them into a more archive style table.

Agree with LiA RE: a running total table as it would be small and very fast...

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved