Understanding a double entry accounting DB Schema

Question

This question is related to a previous question "Why the Accounting DB Schemas do not allow to maintain a clean Products Table?", asked on this network by someone else.

Assuming that

the database schema of the vast majority of Accounting Systems follows this architecture

is true, and that most DB administrators seem to agree due to the lack of counter arguments on said previous question, and I am also trying to understand dual entry bookkeeping database principles (without having used many myself), I have specific questions about specifically this schema.

Disclaimer: These are twelve questions, I think it would be impractical to post twelve separate questions on stackexchange. I am therefore posting it as one question under the purpose of understanding this accounting schema.

(Here is the image again for reference.)

1. Account table contain records of what general accounting understands as ledger accounts (such as: rent, internet, sales, purchases....), correct?

2. Journal table contains records of what general accounting understands as journal entries (such as: john paid 100gbp cash into bank account, jane agreed invoice 123 at 50gbp, james purchased desk 100gbp, ...), correct?

3. Why does journal table have default_credit_account_id and default_debit_account_id, which is what I would believe the ledger account would prescribe, not a journal entry ?

4. The account table records contain credit, debit and balance fields, which I assume are derived totals, calculcated from the sum of related journal entries, correct?

5. A bank statement (such as one that you download as CSV from online banking) has debit, credit and balance columns. Each bank statement row, matches (in principle) a record in the move table, correct?

6. Each paper bank statement item (matched with a matching move record), can be split into multiple transaction purposes (eg. james pays 100gbp into bank for 5 separate past invoices), and hence the move_line table splits a move record (a bank statement row) into parts that match the intended journal records, correct?

7. How is a move_line debit/credit related? I understand that this is a bit more intricate, as a bank credit/debit is not necessarily the same (credit or debit) in the company; it depends on what sort of ledger account the move_line record is associated with, is this thought correct?

8. If the choice of debit/credit inside a move_line record is related to it's associated account record (via account_id), then what is the purpose of default_credit_account_id and default_debit_account_id (to which the move_line record is related to)?

9. Given that there is a balance field in the move_line table, and that the balance depends on previous move_line records (related to the same move record), what defines the order of move_line records? It seems to me that date_posted for all move_line records would be the same as related to the move record? Even if that is not the case, a date without a time seems a bit... large for an order precision?

10. Why are all currency fields defined to 4 decimal places below 1 instead of 2 decimal places corresponding to cents? Does this have to do with database float imprecision?

11. If accounting software often need to summarize the debit and credit fields of large sets or records, wouldnt it be better to define debit and credit fields as uints and units of cents, instead of computationally expensive floats?

12. The meaning of a fiscalyear record, is understood to be eg. an accounting financial year for the purposes of accounting taxation years. Why is period table there? If this was to sub-divide a fiscal year into months or weeks, why isn't there a period_name field? What is the intention of this period table?

Answer 1

My first job in IT was assisting the development of an accounting system, respecting european rules, afterwards, I developed one all by myself. When I first came into contact with an american system - won't name any big name, but it was sold in Europe - my reaction was "Seriously??".

2.false, those are in the move_line table

5.false, can only fit in the move_line table

6.see 5

7.false, debit is receiving, credit is spending, in Europe it would be the opposite of what is on the bank statement

8.in a balance sheet, a ledger could appear in a different location (account) depending on the balance being negative or positive

9.the balance field is a joke, I think

12.the fiscal year can have more or less than 12 months, no need for description of periods - as long as they are indeed months - since you can calculate the date with the start date of the fiscal year

The schema you've shown is a mash-up of poor accounting and invoicing. No way a customer would pay a single product of invoice, he may pay part of an invoice or multiple invoices at once. I don't think there is room for invoices and products in an accounting schema.