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

Answer 1

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

Answer 2

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.