0

I've already built a double-entry accounting schema on a previous app, and it was very complicated. For a new app I want the simplest and most elegant accounting schema possible (transaction volume is low, and uncomplicated).

  • People can earn money via ad on their website (like google adsense)
  • We issue them payments for their earnings at a varying interval (weekly, monthly, etc)
  • Their account can be debited (eg. their website had fraud robot visits), or it could be credited (signup bonus, or a technical error where we didnt pay them enough)

In its simplest form, I have a transactions table with these core columns (ignoring other fluff columns):

  • id (string)
  • type (enum: DEBIT/CREDIT)
  • description (string)
  • amount (integer)

All of this is well and good, there is only 2 confusing parts that I would like someone to tell me if my solution is sane or not.

  1. First is the fact that earnings need to be "settled" in some way. Fraud and other hiccups are common, so "live" earnings are constantly adjusted. Without an intermediate settlement step, these price variations disappear into the ether.

So my first idea as to create a transactions record every single day for the previous days "settled" earnings. It might look like this, including one demo "fraud" example:

"CREDIT", "User Earnings - Jan 3, 2021", 12021
"CREDIT", "User Earnings - Jan 4, 2021", 8319
"CREDIT", "User Earnings - Jan 5, 2021", 9130
"DEBIT", "Fraud Adjustment", 771

Question 1 is, "does this seem sane so far?"

  1. The next confusion deals with how to cleanly link a payment (transaction) with other transactions (the 4 rows above).

If I wanted to pay the above 3 earnings rows + 1 adjustment row, it would look like this:

"DEBIT", "User Payment", 28699

This is fine, except that it's not in the database which transactions this transaction is associated with. One solution might be to make a many-to-one relation on transaction to itself, but a transaction being associated with other transactions feels "wrong".

I envisioned when an admin issued a payment, it would show a list of all un-associated transactions with checkboxes next to them you could choose from, which would make those transactions tied to the payment somehow, and then they wouldn't accidentally be paid for again, as they would disappear from the list.

My biggest fear is re-inventing the wheel. I'm OK with doing a simpler solution if its "normal" in the real world. I don't want to be a single developer trying to "invent" a solution that's actually really stupid and is easily solved in a different way.

Thank you!

1

For your first question, that's an appropriate approach to balancing the Transactions. Every monetary change should be recorded as a Transaction. So at whatever rate you consolidate things like fraud, e.g. once a day (or as frequent as every minute, doesn't really matter as long as you record the Transaction for the consolidated amount every time) will result in how many of those kinds of Transactions to be generated.

To your second question, there's nothing inherently wrong with it being a one-to-many relationship, and business context aside, that's how I'd first imagine it. Bringing business context back in, normally a financial system in this space would handle these things through journaling. The concept of a journal is usually represented by two tables, JournalHeader and JournalLines. The JournalHeader represents generic information such as who created it and when it was created, whereas the JournalLines represent the individual Transactions consolidated via settlement. Usually there's a field on the JournalLines table that ties back to the original source of the transactions that were consolidated (generally another table, e.g. if there were SalesOrders that generated Transactions, the JournalLines would tie back to the original SalesOrder they consolidated). This relationship is one-to-many in nature, and standard.

I'm a little fuzzy on the exact implementation of such (as I haven't worked with that kind of finance in a while), but hopefully that helps paint the picture. Long story short, it is ok and normal practice to have a one-on-many relationship between the settling Transaction to its settled Transactions.

6
  • 1
    Fantastic, thanks so much for your very helpful answer. What you wrote about jornaling is very insightful. I will need to read about and think about the journal example to fit it into my picture. – Tallboy Feb 9 at 2:37
  • So how does the journal relate to showing the user a list of transactions? Like if i have an area which shows a listing of the users debits/credits, how would journal header/lines fit into this list. – Tallboy Feb 9 at 2:41
  • @Tallboy No problem, glad to be of help! There's a lot of scholarly articles on "finance journal schema" out there, but because I haven't worked with that kind of schema in a long time, I'd be hesitant to recommend one that's sub-par. But I'm sure through your own research you'll find a lot of helpful information to your situation. To answer your second question, it depends on exactly what information you'd want to show on that view, but if you just wanted to show a single running list of debits and credits, your Transactions table should be sufficient. If you wanted to show a more... – J.D. Feb 9 at 3:00
  • ... traditional "T" table with debits on one side and their correlating credits on the other side, then that's where your Journal tables can help you display the appropriate debits tied to the appropriate credits, for that user. Batching is another concept that usually is related to journaling, and might be worth reading up on. It's especially helpful to understand if you have the case where a debit might relate to a bunch of unrelated credits or non-continuous credits. Basically a way to settle various sets of credits that may have been generated at different times. – J.D. Feb 9 at 3:00
  • 1
    awesome, thanks so much again!! – Tallboy Feb 9 at 3:30

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.