I’m starting to learn a bit about databases, and I’ve been reading for while about what is the best way to design a database for a simple accounting system with double entry.

For those who are not familiar with accounting, I have a General Journal, and I need to extract a few reports out of it – basically a bunch of SUM IFS (sum this account if this date).

I currently have my journal table designed as a one-liner, but I found this post and this answer that got me thinking.

Some parts of the answer that I would like to understand a bit better:

“…design a data structure that can be the numerical repository for multiple journals…”

• What would be an example of a numerical repository for multiple journals?

“…In a modern RDBMS… …the General Ledger, and even the specialized subledgers, can become Indexed Views on the Journal, completely eliminating the requirement to code a Posting Process (the step where a Journal transaction is locked and has its account totals transcribed to the various ledgers)…”

• How exactly should I deal with the “Indexed Views” approach?
• Create an indexed view for each Income Statement/Balance Sheet account?
• Maybe materialize a Pivot Table with all the acounts of the Income Statement/Balance Sheet in the columns (don’t think that would be reasonable since I have about 150 accounts total)?

My query to extract a stock account (one that accumulates over time, such as Balance Sheet accounts):

SELECT Y, M,(@total := @total + Flow) AS TotalValue FROM (         SELECT year(dateacc) AS Y, month(dateacc) AS M,              (                 SUM(IF(Credit='Account', valor, 0))-                 SUM(IF(Debit='Account', valor, 0))             ) AS BS_Account         FROM journal         GROUP BY YEAR(dateacc), MONTH(dateacc)     ) AS T, (SELECT @total:=0) AS n; 

My query to extract a flow account (such as Income Statement Accounts):

SELECT DATE_FORMAT(datecash, '%m-%y') as DATA,      (         SUM(IF(credit='Account', value, 0))-         SUM(IF(debit='Account', value, 0))     ) AS Flow  FROM journal_test GROUP BY YEAR(datecash), MONTH(datecash) 

In a nutshell:

1. What is the best way to design this?
2. View vs Materialized View vs. Derived Table
3. How to approach the general journal vs. subledgers dilema