Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '13 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. –  cosmolev May 31 '13 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 '13 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. –  cosmolev May 31 '13 at 20:34
    
It's not necessarily WRONG it's just going to be harder to maintain. –  JNK May 31 '13 at 20:37

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thx, Joel! That's exactly where I arrived after reading bunch of articles and examples. –  cosmolev Jun 3 '13 at 18:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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