如何从电子商务应用程序中设计收入表？

Question

I'm actually worried about designing the perfect tables to handle transactions and commissions. especially that we really don't want tables to 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.

Answer 1

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.