What is the best way to structure a Virtual Credits System in PHP/MySQL?
I would obviously need one table for each Transaction (debit/credit). I'm thinking I would need the following columns in that table:
TransactionID (unique transaction ID starting at 1 with Auto-Increment)
UserID (users unique ID from the main Users table)
Type (earned, purchased, spent, etc.)
ItemID (if spent on an item)
Amount (positive or negative).
QUESTIONS:
1) Do I need any other columns in this Table?
2) Do I need any other Tables for this purpose?
For example, would I need another table to keep track of each user's total? Or would I simply calculate each user's total as the sum of all their transactions?
At first, this Credit System will be purely "play money", but I do expect to add a "Buy Credits" option in the future, so perhaps I should add a column for the 3rd party payment processors Unique Transaction ID ? Or should I create a separate table for purchased Credits, and then tie the two together via my script (e.g. total credits = earned credits + purchased credits - spent credits)?
On top of answering my questions above, I would appreciate any and all insight and ideas, including links to specific articles on how to go about this, as I've never programmed anything like this before.
Thanks!
Some notes.
I think if you want to build a solid system that handles finances, it makes sense to use some basic concepts from financial world. I don't want to say you have to completely implement all accounting features, but many things seem reasonable to me.
First of all, financial transaction involves moving money from one account to another, so instead of user_id I'd use account_id. Each user may have more than 1 account , each account has a type (lookup), and currency. Also, I'd avoid term "user" in favor of "party" (party-role-relationship model).
Secondly, there are always at least 2 parties involved : money moves from one account to another. In case of item purchase , person's account balance decreases, and organization account balance increases.
Also, I'd not use item_id, but something like event_id . Each transaction is associated with one business event. Event is a common parent table for various events which may occur in your system. Each of Event's detail table will hold information specific to event type. For instance, you can have purchase_event , deposit_event, etc.
Because transaction table is a subject of many (potentially thousands) inserts, I wouldn't query it every time you need account balance. It makes sense to keep consolidated balance in account table.
I hope that makes sense.