0

I'm developing a new double-entry accounting system for my company, which has Customers and Workers. Customers pay the company for services rendered, and in turn the company pays Workers for providing these services (sort of an Uber model).

The idea is to model an Account for each Customer, Worker, and the company itself. Each transaction therefore, is either a transfer of funds between two of these accounts, or a transfer of money in/out of the system as a whole. The latter case would be, for example, when a Customer makes a payment or when a Worker receives a paycheck.

For example, consider the following accounts:

| id | type     | name    |
| -- | -------- | ------- |
| 1  | internal | Company |
| 2  | customer | Emma    |
| 3  | worker   | Alex    |

Suppose Alex provides a service to Emma. Emma is charged for it, and Alex is paid some percentage. In the transactions table, this would look like:

| id | from_account_id | to_account_id | amount | type    |
| -- | --------------- | ------------- | ------ | ------- |
| 1  | 2               | 1             | 50.00  | service |
| 2  | 1               | 3             | 40.00  | service |

Negative account balances are allowed, but transaction amounts must always be positive. So at this point in time Emma has an account balance of -50, Alex has an account balance of 40, and the company has an account balance of 10.

For the system as a whole to become solvent, Emma must deposit (make a payment) and Alex must withdraw (be given a paycheck). As these occur, we add additional transactions:

| id | from_account_id | to_account_id | amount | type       |
| -- | --------------- | ------------- | ------ | ---------- |
| 3  | NULL            | 2             | 50.00  | payment    |
| 4  | 3               | NULL          | 40.00  | withdrawal |

Transaction 3 is essentially "Emma funding her account", while transaction 4 is "Alex withdrawing from his account". Any account's current balance can be derived by adding all of the amounts where their account appears in the to_account_id, and subtracting all of the amounts where their account appears in the from_account_id. Our "accounts receivable" would simply be the sum of all negative balances, and "accounts payable" would be the sum of all positive balances.

This seems like a good approach, but it doesn't really reflect the actual flow of money. When Emma pays us from her credit card, from the banks' perspective there is no "Emma's internal account" - the funds are simply transferred directly from Emma's bank account to the Company's bank account. Is it a problem for us to model "Emma's internal account" anyway?

There is also the possibility that Emma will pay $50 to Alex directly when services are rendered, and then Alex needs to pay the Company their share ($10). Under my proposed system I would model this as follows:

| id | from_account_id | to_account_id | amount | type       |
| -- | --------------- | ------------- | ------ | ---------- |
| 1  | 2               | 1             | 50.00  | service    |
| 2  | 1               | 3             | 40.00  | service    |
| 3  | NULL            | 2             | 50.00  | payment    |
| 4  | 3               | NULL          | 50.00  | withdrawal |

So under this model Emma has fully funded the cost of the service, and Alex has "withdrawn" $50 from his account (though he only gets to keep 40 of it). Thus, Alex ends up with a -$10 account balance. Alex's account will become solvent again when he does more work, or pays his balance to the company.

Is this a good idea, even though the reality is just that Emma handed Alex $50?

  • 1
    This question is about bookkeeping or accounting, not really about database design. Depending on how deep you want to go, you might want to learn some principles of accounting. You might want to look up accruals and deferrals. – Walter Mitty Sep 11 '17 at 21:50
  • My understanding (limited though it is) of double-entry bookkeeping is that every transaction acts as a credit to one account and a debit to another. These accounts are all "buckets" of money related to accounting: sales, accts payable, accts recv., etc - not the accounts of the customers or workers. Clarify what you're accounting dept. actually wants. – RDFozz Sep 11 '17 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.