How to design a revenue table out of an ecommerce app?

I'm actually worried about designing the perfect tables to handle transactions and commissions. especially that we really don't want tables 开发者_如何学JAVAto be out of sync in any case.

Basically this is like ebay, where merchants sell to buyers and we get a commission out of each transaction.

The app is already built using Codeigniter, and it has:

Merchants(id,name,balance)
Transactions(id,merchant_id,buyer_id,amount,transaction_id)

Now what other tables to make if needed to make us able to fetch our revenue and the seller's revenue, and what's the best DB mechanism to process a refund?

And should we make another table of each deposit to the merchant's balance rather than +x the balance row.

Whenever you are dealing with any financial tasks, always store as much information about it as possible.

There are two ways of keeping all your tables in sync.

• Database level - using options such as stored procedures and triggers

• Application level - Use transactions (innoDB) and make sure that the transactions are atomic. (I would suggest to go with this)

Regarding the database design -

Add a column commission to Transactions that tracks your commission. Data for this can be either calculate beforehand when pricing the item or on the fly during the transaction.

The buyer pays product cost + commission, seller gets product cost and you get commission. If your commission is a variable (not a flat rate and changes from item to item), you could have a column in the Items table which stores it. Things should be trivial from now on. You just have to maintain a History table that keeps a list of transactions with respect to the user. It would look something like

AccountHistory(id, transaction_type, transaction_id, amount)

You could split AccountHistory into two - one for incoming money and one for outgoing money.

So everytime a transaction takes place the following happens

1. Entry into to Transactions table.
2. Entry into AccountHistory for buyer
3. Update buyer balance
4. Entry into AccountHistory for seller
5. Update seller balance

All this should be atomic.

For rollbacks, you could add a column transaction_type to distinguish rollbacks from normal transaction or you could maintain a separate table.

Having worked with a database where the design had financial transactions scattered around a bunch of different tables, I can tell you that it is much better to have all transactions in a single table. You should add transaction type to your transaction table (and a look up table for the transaction types).

Then each transaction can have a separate line for commission and seller product cost. Transaction that are undone later would get entered in as negative amounts. It is also helpful for these types of things to add a parent transaction field so that you can easily identify which transaction it was changing.

All data entry that forms part of a transaction should be in a stored proc and have transactions so that if one part of the insert fails, the entire thing is immediately rolled back. This is critical. It needs to be in a stored proc so that no one (except systems dbas) has direct access to tables that store financial records.

When you start to think of handling financial transactions the very worst thing you can do is design for the user interface alone. You need to design for the financial reporting you will need to have. You need to consult the people who will be auditing your finances to see what information they need and what internal controls they expect you to have in place to prevent internal as well as external fraud.