Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

How do you model user account table

1:10 am on Dec 4, 2012 (gmt 0)

New User

5+ Year Member

joined:Mar 17, 2009
posts: 27
votes: 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.

1:39 am on Dec 4, 2012 (gmt 0)

Moderator from US 

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

joined:Dec 10, 2005
votes: 24

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.
2:04 am on Dec 4, 2012 (gmt 0)

Moderator from GB 

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

joined:Jan 30, 2002
votes: 1

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