How do you model user account table

Forum Moderators: open

Message Too Old, No Replies

How do you model user account table

stivare

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

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

LifeinAsia

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

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