6

I'm building a gambling website. All financial transactions from and to external sources (credit cards, money transfer systems) are subject to commission. Amount of the commission depends on the source selected. And moreover, it changes periodically. These external commissions go to the payment systems.

Also some inner transactions are subject to commission. Inner commissions depend on the type of the game user playing. These internal commissions go to gambling website, it is the income.

I'm looking for the best way to store the history of transactions and commissions. Should they be stored in one table TRANSACTIONS (with a connection between payment and commission) or should I have separate table COMMISSIONS.

The purpose of storing is to represent the history of transactions to the user and of course for internal bookkeeping (in order to always know how much have we paid to payment systems and how much have we earned ourselves).

Any advice greatly appreciated.

Improve this question
8
  • 1
    If you store the commission rate in its own table but store the commission amount in the transactions table that may work nicely.
    – JNK
    May 31, 2013 at 19:41
  • 2JNK: Yeah, this is what I'm going to do. But willn't it pollute TRANSACTIONS table with redundant fields? For example in transaction which represents the commission I need to keep a key of the commission rate applied. But in all other types of transactions this field will be NULL.
    – Leo
    May 31, 2013 at 20:24
  • That's why we have NULL! I think it's fine but I'm not confident enough to post it as an answer, notice :)
    – JNK
    May 31, 2013 at 20:26
  • The truth is I probably also need a key to the game for game dedicated transactions. So there will be some redundant fields anyway. Otherwise I need to have separate GameTransactions, CommissionTransactions, DepositTransactions and withdrawal transactions fields. Which is obviously wrong. Would be nice to hear some hidden drawback from someone who has previously experienced such problems.
    – Leo
    May 31, 2013 at 20:34
  • It's not necessarily WRONG it's just going to be harder to maintain.
    – JNK
    May 31, 2013 at 20:37

1 Answer 1

Reset to default
7

For any application where you are tracking money changing hands, you should always use a Double Entry Accounting system. Double entry accounting has been the standard for tracking money for over 500 years. There is no good reason to use any other method.

This entails a TRANSACTION table and a TRANSACTION_DETAIL table. The transaction table has one record per transaction that includes header information, like the date, the total amount, any categorization attributes or relationships, etc.

The transaction detail has two or more records per transaction. These records break down where the money comes from and goes to. In your case, you might have three records per transaction. One shows the money coming in from the payment service. The other two show the money going out to a commission account and to an income account.

Improve this answer
1
  • Thx, Joel! That's exactly where I arrived after reading bunch of articles and examples.
    – Leo
    Jun 3, 2013 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.