Questions on the implementation about medici

[Uzlopak]

Well. I think the implementation of book keeping is regarding mongodb good. But I think for scaling up I would nowadays probably do different approaches. Anyway: I would recommend to you to look how e.g. Microsoft Dynamics AX 2012 or so did handle the t-tables. I worked with those design like 8 years ago and I think it is still be best way to do it in SQL.

[Uzlopak]

Well. I think to realize a good accounting software you actually need to implement some aspects more technically and some aspects closer to the reality and more according to the thing we call in Germany GoB = Grundsätze der ordentlichen Buchhaltung. According to wiki its called accounting standards in English.

E.g. technical aspects is e.g. enforcing to not use floats, always integers. See https://www.youtube.com/watch?v=TQDHGswF67Q for the funny aspects of using floats.
Currently generating the balance of an Account is the big complex solution in medici.

The accounting standards makes you think more like an real world accountant. It starts with the what we call in german Kontenplan, which is according to wiki called Chart of accounts in english. What does this mean? You usually dont create your own account chart but you look what the de facto standard of your country is regarding the accounting charts. E.g. in Germany we have a company called Datev, which has a handful pre defined accouting charts, which are basically used by every german company see https://www.datev.de/web/de/m/ueber-datev/datev-im-web/datev-von-a-z/skr-standard-kontenrahmen/ . The reason is simple: If the tax office comes to investigate your accounts, you want them to be out very fast out of your company. If every company uses a similar accounting chart,then they dont have to stay longer because they have to understand your custom accounting plan.
With the used account chart, you can actually define beforehand how many T-Tables you actually need. It does not mean that you need a sql table per account, but e.g. in medici you have a depth of up to 3, "Income", "Income:Rent", "Income:Rent:Bla". But if you know that you always have a depth of three and you can not credit or debit into the first or second layer you can reduce some complexity regarding determining the balance of the accounts. So if you currently want to determine the balance of "Income" it will go through all the entries with "Income" as first account_path. But if you know that you can not credit or debit on first and second part of the account, you can persist the balance of each 3-part-account and sum it up. In "normal" accounting you would for determine the balance of a 1-part-account also not start to accumulate over all journal-entries, but accumulate over the 3-part-accounts.

Also the reality gives us a good hint, how to solve the balancing issue. In Accounting you would also actually close the books every year, make Rechnungsabgrenzungsposten (english: accruel) and then open fresh books. So you dont carry old data for ever. So you can actually reduce the data overhead. So a functionality to close books and open fresh books should be imho also implemented. If you login into your banking account, than you also dont want to see the super old transactions. You see the e.g. latest 30 days. If you want to see older data, you can load it from closed books. Maybe the closed books are also on slower memory, as you usually dont need the old stuff that regularly. So what we would maybe call database backup the corresponding process is in accounting the closing of books. And we are not forced to do it every year, but every month or every x days.

I know this answer blowed out of proportion, but I really think, that reading some accounting books and asking an accountant how they actually work, can give good hints how to do it.

[koresar]

Now I want to implement Rechnungsabgrenzungsposten.

Thanks. So useful! I'd love to see that info in the md file or something.

[Uzlopak]

Tbh I first thought you make jokes about implementing Rechnungsabgrenzungsposten ;)

Regarding a new implementation: I would recommend to use Prisma’s Open Source ORM (not the Prisma PaaS) or any other multi database ORM. You would then stronger separate the database layer from the logic layer and could switch the database if you need to. So you could use mysql or postgre-sql or sqlite (well maybe not sqlite) or still use mongo.

Also you could finally implement account-collection/table. One reason that we dont have accounts in medici is the issue I mentioned above regarding multi part accounts. So if you have 3 part accounts and deny transactions on 1 part or 2 part accounts, you can then implement database-transaction-locking per account.
Again as clarification: If you allow transactions on 1 part or 2 part accounts like it is in the current medici logic, you have to also lock them, even if you "just" use 3-part accounts to be sure that no inconsistensy happens because somebody writes later code which creates accounting-transactions into 1 or 2 part accounts. So imagine you have "Customer" and "Customer:CustomerId" as account you would need to lock "Customer"-top-level account path if you want to transfer money from "Customer:CustomerId:credit" to "Customer:someCustomerId:debit". If you have hundreds of transactions in the 3 part accounts then they would all try to lock "Customer" top level account and thus resulting in failed transaction attempts as they can not lock the "Customer"-account all at the same time.

But dont forget the implications we have then on the other way:
Imagine you have a bitcoin exchange. You have to store transaction fees to a corresponding customer account. Something like "Customer:CustomerId:fees". So transferring money to a general account like "TransactionFees" is discouraged, because again: Imagine having hundreds of transactions per second. If they (the transactions) all try to transfer money to a 1 part "TransactionFees"-account they would all try to lock it. So again: every account needs a corresponding fees account.

I think my thoughts regarding the locking issue is valid for all databases.

If you then want to persist/transfer your gains to your central e.g. "Income:TransactionFees:august2022"-account, you would need to lock over all the transactionfees accounts. But you could use strategies like, transfer to "Income:TransactionFees:august2022" only in batches of 100 from "Customer:CustomerId:fees" Accounts which had no transaction for a day. So you lock always a fraction of your not-hot accounts, reducing locking-issues effectively. A hot account would be something like customer did a transaction few seconds ago, so the chance of having another transaction in that time, is higher than a customer, who did not do anything for x-amount of time.

I hope my remarks make sense to you.

[poeti8]

I see. Thanks for that.
One last thing, you said you'd probably take different approaches nowdays, may I ask what you have on your mind so I can just have some general ideas?

[prashb94]

+1 to implementing Rechnungsabgrenzungsposten!

[poeti8]

Thanks for your explanation, it was a good read.

[trasherdk]

I, for one, would love to se a medici-sql version, modeled from @Uzlopak answer 👍

[koresar]

In medici, I would discourage creating accounts like Customer:CustomerId:fees.

Instead, I'd recommend to use meta data more.

Customer:fees + meta.CustomerId = "1234"

In medici, I'd NOT recommend storing anything variable in account names. By saying "variable" I mean: IDs, dates, names, emails, etc.

In my opinion account Income:TransactionFees:august2022 is a bad idea. I'd recommend having Income:TransactionFees and doing a query over the August 2022.

[Uzlopak]

For medici thats true, Income:TransactionFee:august2022 was just an example to have a 3-part account.