Sfrgttk

I have this schema for double entry bookkeeping system:

CREATE TABLE account (

  id        INTEGER PRIMARY KEY,

  name      TEXT NOT NULL,

  parent_id INTEGER REFERENCES account(id),



  UNIQUE (name, parent_id)

);



CREATE TABLE journal (

  id    SERIAL PRIMARY KEY,

  title TEXT NOT NULL,

  date  DATE NOT NULL DEFAULT CURRENT_DATE

);



CREATE TABLE posting (

  id          SERIAL PRIMARY KEY,

  account_id  INTEGER NOT NULL REFERENCES account(id),

  journal_id  INTEGER NOT NULL REFERENCES journal(id),

  amount      NUMERIC (12, 2) NOT NULL,



  -- debit and credit are described by positive and negative value, therefore

  -- zero value is not allowed

  CHECK (amount <> 0)

);

So, basically each transaction will be recorded in journal table and it's amount that is affecting account table will be recorded in posting table.

Since the structure of account is a hierarchical tree, the amount of each node must be calculated with all of it's descendant.

This is the best I can come up with:

WITH RECURSIVE

  -- recursively build tree table, assigning path for each row

  tree(id, parent_id, name, path) AS (

    SELECT account.id        AS id,

           account.parent_id AS parent_id,

           account.name      AS name,

           ARRAY[account.id] AS path -- path from root

      FROM account

     WHERE account.parent_id IS NULL



     UNION ALL



    SELECT account.id                     AS id,

           account.parent_id              AS parent_id,

           account.name                   AS name,

           tree.path || ARRAY[account.id] AS path

      FROM account, tree

     WHERE account.parent_id = tree.id

  ),

  -- make a flat table. each account will appear as whenever it's child is

  -- appeared

  flatten AS (

    SELECT unnest(tree.path) AS id,

           tree.id           AS account_id

      FROM tree

     ORDER BY unnest(tree.path), tree.id

  )



SELECT tree.id                               AS id,

       tree.name                             AS name,

       coalesce(abs(sum(posting.amount)), 0) AS amount

  FROM tree

  LEFT OUTER JOIN flatten ON tree.id = flatten.id

  LEFT OUTER JOIN posting ON posting.account_id = flatten.account_id

 GROUP BY tree.id, tree.name

 ORDER BY tree.id

My question is, Can this be improved? Will it perform well?

PS: Some data for testing code

INSERT INTO account VALUES (100, 'ASSET', NULL);

INSERT INTO account VALUES (101, 'Cash', 100);

INSERT INTO account VALUES (102, 'Bank', 100);

INSERT INTO account VALUES (200, 'LIABILITY', NULL);

INSERT INTO account VALUES (201, 'Loan', 200);

INSERT INTO account VALUES (300, 'EQUITY', NULL);

INSERT INTO account VALUES (301, 'Opening Balance', 300);



INSERT INTO journal VALUES (1, 'opening balance', '2018-12-05');

INSERT INTO posting VALUES (1, 101, 1, 1000000.00);

INSERT INTO posting VALUES (2, 301, 1, -1000000.00);



INSERT INTO journal VALUES (3, 'save to bank', '2018-12-05');

INSERT INTO posting VALUES (5, 102, 3, 1250000.00);

INSERT INTO posting VALUES (6, 101, 3, -1250000.00);



INSERT INTO journal VALUES (2, 'loan money', '2018-12-05');

INSERT INTO posting VALUES (3, 101, 2, 500000.00);

INSERT INTO posting VALUES (4, 201, 2, -500000.00);

The output (for running the query above) should be something like this:

 id  |      name       |   amount   

-----+-----------------+------------

 100 | ASSET           | 1500000.00

 101 | Cash            |  250000.00

 102 | Bank            | 1250000.00

 200 | LIABILITY       |  500000.00

 201 | Loan            |  500000.00

 300 | EQUITY          | 1000000.00

 301 | Opening Balance | 1000000.00

(7 rows)

If your account table doesn't change often, you could take advantage of Postgres Materialized Views.

CREATE MATERIALIZED VIEW account_tree 

AS

WITH RECURSIVE

  -- recursively build tree table, assigning path for each row

  tree 

AS (

    SELECT account.id        AS id,

           account.parent_id AS parent_id,

           account.name      AS name,

           ARRAY[account.id] AS path

      FROM account

     WHERE account.parent_id IS NULL



     UNION ALL



    SELECT account.id                     AS id,

           account.parent_id              AS parent_id,

           account.name                   AS name,

           tree.path || ARRAY[account.id] AS path

      FROM account, tree

     WHERE account.parent_id = tree.id

  )

SELECT 

    t.id,

    t.account_id,

    CASE WHEN t.parent_id = t.account_id 

         THEN a.name ELSE t.name END AS name

FROM

    (SELECT id, parent_id, name, unnest(path) as account_id

     FROM   tree) t

JOIN

    account a

    ON a.id = t.parent_id;

Materialized views allows the use of index:

CREATE INDEX account_tree_ix

  ON account_tree (id, account_id);

This view returns next result:

 id | account_id | name           

--: | ---------: | :--------------

101 |        100 | ASSET          

101 |        101 | Cash           

102 |        100 | ASSET          

102 |        102 | Bank           

201 |        200 | LIABILITY      

201 |        201 | Loan           

301 |        300 | EQUITY         

301 |        301 | Opening Balance

Now you can use this view to obtain the balance sheet avoiding the recursive CTE:

SELECT    at.account_id, 

          at.name,

          coalesce(abs(sum(p.amount)), 0) AS amount

FROM      account_tree at

LEFT JOIN posting p

          ON p.account_id = at.id

GROUP BY  at.account_id,

          at.name

ORDER BY  at.account_id;

account_id | name            |     amount

---------: | :-------------- | ---------:

       100 | ASSET           | 1500000.00

       101 | Cash            |  250000.00

       102 | Bank            | 1250000.00

       200 | LIABILITY       |  500000.00

       201 | Loan            |  500000.00

       300 | EQUITY          | 1000000.00

       301 | Opening Balance | 1000000.00

db<>fiddle here