Welcome to WebmasterWorld Guest from 54.145.209.34

Forum Moderators: open

How do you model user account table

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

5+ Year Member



Hi,

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.

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

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



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)

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



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

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month