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.
Best Answer
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
TRANSACTIONtable and aTRANSACTION_DETAILtable. 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.