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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.