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):
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.
- 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?"
- 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.