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:
- What is the best way to design this?
- View vs Materialized View vs. Derived Table
- How to approach the general journal vs. subledgers dilema