Storing credits in database

Question

Just a quickey. I am developming website, where you can buy credits and spend them later for things on the website. My question is, is it ok to store amount of credits with user (user table, column credits and iteger amount) or it is necessary (or just better) to have separate table with user id and amount ?

Thanks

Andrew Moore · Accepted Answer · 2011-06-29T11:32:04.720

7

Both actually.

Considering that you'll be dealing with monetary transactions to get those credits, you want to be able to get a log of all transactions (depending of the laws in your country, you will NEED this). Therefore you'll need a credits_transactions table.

user_id, transaction_id, transaction_details, transaction_delta

Since programmatically calculating your current credit balance will be too costly for users with a lot of transactions, you'll also need a credit_balance row in your user table for quick access. Use triggers to automatically update that column whenever a row is inserted from credits_transactions (technically, update and delete shouldn't be allowed in that table). Here's is the code for the insert trigger.

CREATE TRIGGER ct_insert
AFTER INSERT ON credits_transactions
BEGIN
  UPDATE users SET credit_balance = credit_balance + NEW.transaction_delta WHERE user_id = NEW.user_id;
END
;;

edited Jun 29 '11 at 11:32

answered Jun 29 '11 at 11:25

Andrew Moore

93,497
30
163
175

Yeah that is exactly what i thought would be the most efficient way. I think that the other ideas with calculating balance from log would be terribly slow, thinking of that site goal is to have more than 100k users and guess is that they will do transactions or daily basis. – Tomas Jun 29 '11 at 11:34
but I still have one question, you said use triggers, could you explain me a bit more about this please? – Tomas Jun 29 '11 at 11:34
Your Trigger would work only for adding credits right ? what about spending? can be minus value in transaction_delta? – Tomas Jun 29 '11 at 11:39
@Tom: MySQL support [triggers](http://dev.mysql.com/doc/refman/5.0/en/triggers.html); a named database object that is associated with a table, and that activates when a particular event occurs for the table. This allows you to check the validity of data or update another table when rows are inserted/deleted/updated. You can learn more about [triggers](http://dev.mysql.com/doc/refman/5.0/en/triggers.html) and their [syntax](http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html) in the MySQL Manual. – Andrew Moore Jun 29 '11 at 11:39
@Tom: Yes, spending would be a negative (financial debit) value in `transaction_delta`. My trigger works for both. – Andrew Moore Jun 29 '11 at 11:40
@Andrew Moore I dont know why but im getting syntax error:CREATE TRIGGER mpc.credits_update AFTER INSERT ON transactions BEGIN UPDATE users SET credits=credits+NEW.amount WHERE users.id=NEW.user END – Tomas Jun 29 '11 at 12:09
Tom - which version of MySQL do you have? Triggers are only supported since 5.0.2. – Steve Hill Jun 29 '11 at 16:58
@Stephen Orr Shouldnt be problem, my MySQL is 5.1.54, other idea? – Tomas Jun 29 '11 at 17:12
@Tom: Is the table using InnoDB? – Andrew Moore Jun 29 '11 at 20:25
@Andrew Moore: Hi i dont know why but some of the tables are InnoDB and some are not, I changed it to InnoDB but still doesnt work. – Tomas Jun 30 '11 at 13:44
@Andrew Moore: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN UPDATE users SET credits=credits+NEW.amount WHERE users.id=NEW.user END' at line 1 – Tomas Jul 01 '11 at 07:05
@Tom: You are missing your semi-colon after your update statement. Also, `CREATE TRIGGER mpc.credits_update AFTER INSERT ON transactions FOR EACH ROW BEGIN UPDATE users SET credits=credits+NEW.amount WHERE user.id=NEW.user; END;;` – Andrew Moore Jul 01 '11 at 13:23
@Andrew Moore: Still have error :( "CREATE TRIGGER mpc.credits_update AFTER INSERT ON transactions FOR EACH ROW BEGIN UPDATE users SET credits = credits + NEW.amount WHERE user.id = NEW.user; MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 " – Tomas Jul 05 '11 at 07:05
@Andrew Moore: hi, any other idea ? I think i tried all ways and still error in syntax. Thanks for help ! – Tomas Jul 14 '11 at 05:31
Solved... i had to change deliminer to // – Tomas Jul 14 '11 at 14:48
also it must be noted that, some(most) shared hosts dont allow Mysql Triggers, specially to keep in mind, if you are building application for end-users. – Mohd Abdul Mujib May 18 '15 at 04:47

score 2 · Answer 2 · answered Jun 29 '11 at 11:19

I also have sites containing credits and found it easiest to store them in the user table, mostly because you need access to it on every page (when the user is logged in). It is only an integer so will not do much harm. I think actually creating a new table for this value might be worse perfomance wise because it needs an index aswel.

A good rule of thumb is to create a user table for the info you need on every page, and normalise the data you dont need on every page (for example adress information, descriptions etc).

Edit: Seeing the other reactions,

If you want to have transaction logs aswel I would store them seperately as they are mainly for logging (or if the user wants to view them). Calculating them on the fly from the log is fine for smaller sites but if you really have to squeeze performance just store the actual value in the user table.

score 1 · Answer 3 · answered Jun 29 '11 at 11:19

1

If you store in separate table, you can keep log of changing the credits. If you store in column, you will have only the current amount of credits.

answered Jun 29 '11 at 11:19

Maxim Krizhanovsky

26,265
5
59
89

score 1 · Answer 4 · answered Jun 29 '11 at 11:20

If you want to keep a record of Credits History Log like

how many credit bought today.
how many spend yesterday.
what did you bought with credits

I think its better to put this in a separate table. In this way you can get these kind of results by applying mathematical operations.

score 1 · Answer 5 · edited May 23 '17 at 12:14

1

Credits are like money. If a user needs to purchase them, then they are money. Money is tracked using accounts. Account has associated transactions, deposits and withdrawals -- and balance. Search the SO or google for database and account. Here are just a few examples:

one
two
three

edited May 23 '17 at 12:14

Community

1
1

answered Jun 29 '11 at 11:35

Damir Sudarevic

21,891
3
47
71

score 0 · Answer 6 · answered Jun 29 '11 at 11:19

0

I'd have a table which stores the purchases and bought credits, with user id. Then calculate each time based on this, it should be fast if it's indexed, this way you will be able to easily have a purchase history.

answered Jun 29 '11 at 11:19

ianbarker

1,255
11
22

Storing credits in database

6 Answers6