Database Design for Debit / Credit / Payout service

Question

I have a webservice that I am designing which pays users to complete certain tasks. For example, if a user clicks a link, they are paid, say, $0.10 to their account. A user could perform any one of these tasks up to 20 times per day. In order for the user to request the funds be paid to them, they must have an account balance of $5.

I'm trying to decide the best way to keep track of the transactions and accounts. My design currently looks as follows:

Accounts
---------
| account_id | member_id | balance  |
-------------------------------------
| 1          | 1         | 497.8500 | -- System Account
| 2          | 5         | 2.1500   |

Transactions
------------
| transaction_id | account_id | type_id | task_id | date | amount |
-------------------------------------------------------------------
| 1              | 1          | Debit   | 1       | date | -1.10  |
| 2              | 2          | Credit  | 1       | date | 1.10   |
| 3              | 1          | Debit   | 1       | date | -1.05  |
| 4              | 2          | Credit  | 1       | date | 1.05   |

This design is based off the accounting principals for double-entry. Now my dilemma is: technically the user isn't paid this money until they have requested a "Payout". A "Payout" consists of the user submitting a request, the request being approved, the money being deducted from their account balance and sent to them via PayPal. So my question is, is it a good idea to actually deduct the amounts from the system balance if the user has not requested a payout yet? The money in their account can be used to fund additional things on the site, it also expires after 30 days of inactivity.

My idea was to keep the transaction table as it is and design another table called payouts with the following structure

Payouts
-------
| payout_id | account_id | date | amount |
------------------------------------------
| 1         | 2          | date | $2.00  |

But then how do I reflect the payout in the transactions table? It seems incomplete.

Should I separate the tasks from the transactions table and only enter a transaction record if the user has requested the payout? I'm not sure if I would lose auditing abilities by doing it that way.

Does anyone have some insight?

Answer 1

Just use another account/ledger/balancesheet code

So on getting the 10c 
Your account is - 10c
Their payout account is + 10c
When the balance on their payout account > $5, then can request one.
When they do debit the amount from payout account, credit it to Payoutpending account
When it confirmed debit payout pending and credit payed out.
If it's rejected debit payout pending and credit payout account

.

Oh and you really need to think about storing balance, as opposed to calculating and reporting it, you'll get in right mess doing that.

Answer 2

I have limited bookkeeping skills, so while I hope I can help, I feel like I'm not fully qualified to give you an answer..

So while I think you are reasonably close to a pure accounting model, I think the payouts should also be transactions in this case.

If a user needs an overview from when cash is added to their account, this list should also contain the payouts. If the user received 5 times $1, and one payout of $5, his account balance should end up with 0.

So since you are properly doing both transactions, this means that the actual payment deducts the $5 from their account, but this needs to go somewhere too. So I feel that once this is paid, there should be a separate account 'paid'.

But.. the programmer in me wonders.. do you really want the double bookkeeping in this case in your database. You can deduce all the numbers without using the system account when you are actually making the payment.

So you are not actually missing any real information.. if indeed the cash always comes from a system account, and the user cannot transfer their balance to a different account..

Answer 3

What are the business requirements regarding the tracking of the payout?

Does your business need to track payout as a separate event (double entry) with a date, amount, etc., or do they just need to know that payout was requested.

If any of this is true, or it is likely, given other companies practices (think merger) it might be good to attach a payout table just like you did to track payout.

Would you also give some details on the requirement on double-entry. I would try to keep business rules as explicitly captured in your design as possible.

Hope this helps. We can keep the dialog going in the comments,