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).
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