How to create and structure a Virtual Credits System using PHP/MySQL

Question

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!

Answer 1

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.

Answer 2

It's difficult to say something about the architecture of the database unless you really know what the system should do. But I still try.

I do not think it's a good idea to store so much information in one table.

I would have done better this way - transactions(id, user_id, amount, timestamp), this table contains the minimum information about each payment.

Everything else is kept separate, for example transactions_types(transaction_id, type) and transactions_items(transaction_id, item)

About credits. if bought will be the same currency, you just add one more type of transaction, and if necessary the table as transactions_psid(transaction_id, psid).

If the currency is different, all depends on the load on the system. Have only one table with unique transaction_id is good, until it becomes too many records.