Breaking 1NF to model subset constraints. Does this sound sane?
- by Chris Travers
My first question here. Appologize if it is in the wrong forum but this seems pretty conceptual. I am looking at doing something that goes against conventional wisdom and want to get some feedback as to whether this is totally insane or will result in problems, so critique away! I am on PostgreSQL 9.1 but may be moving to 9.2 for this part of this project.
To re-iterate: Does it seem sane to break 1NF in this way? I am not looking for debugging code so much as where people see problems that this might lead.
The Problem
In double entry accounting, financial transactions are journal entries with an arbitrary number of lines. Each line has either a left value (debit) or a right value (credit) which can be modelled as a single value with negatives as debits and positives as credits or vice versa. The sum of all debits and credits must equal zero (so if we go with a single amount field, sum(amount) must equal zero for each financial journal entry). SQL-based databases, pretty much required for this sort of work, have no way to express this sort of constraint natively and so any approach to enforcing it in the database seems rather complex.
The Write Model
The journal entries are append only. There is a possibility we will add a delete model but it will be subject to a different set of restrictions and so is not applicable here. If and when we allow deletes, we will probably do them using a simple ON DELETE CASCADE designation on the foreign key, and require that deletes go through a dedicated stored procedure which can enforce the other constraints. So inserts and selects have to be accommodated but updates and deletes do not for this task.
My Proposed Solution
My proposed solution is to break first normal form and model constraints on arrays of tuples, with a trigger that breaks the rows out into another table.
CREATE TABLE journal_line (
entry_id bigserial primary key,
account_id int not null references account(id),
journal_entry_id bigint not null, -- adding references later
amount numeric not null
);
I would then add "table methods" to extract debits and credits for reporting purposes:
CREATE OR REPLACE FUNCTION debits(journal_line) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
SELECT CASE WHEN $1.amount < 0 THEN $1.amount * -1 ELSE NULL END; $$;
CREATE OR REPLACE FUNCTION credits(journal_line) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
SELECT CASE WHEN $1.amount > 0 THEN $1.amount ELSE NULL END; $$;
Then the journal entry table (simplified for this example):
CREATE TABLE journal_entry (
entry_id bigserial primary key, -- no natural keys :-(
journal_id int not null references journal(id),
date_posted date not null,
reference text not null,
description text not null,
journal_lines journal_line[] not null
);
Then a table method and and check constraints:
CREATE OR REPLACE FUNCTION running_total(journal_entry) returns numeric
language sql immutable as $$
SELECT sum(amount) FROM unnest($1.journal_lines); $$;
ALTER TABLE journal_entry
ADD CONSTRAINT CHECK (((journal_entry.running_total) = 0));
ALTER TABLE journal_line
ADD FOREIGN KEY journal_entry_id REFERENCES journal_entry(entry_id);
And finally we'd have a breakout trigger:
CREATE OR REPLACE FUNCTION je_breakout() RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO journal_line (journal_entry_id, account_id, amount)
SELECT NEW.id, account_id, amount
FROM unnest(NEW.journal_lines);
RETURN NEW;
ELSE
RAISE EXCEPTION 'Operation Not Allowed';
END IF;
END;
$$;
And finally
CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE ON journal_entry
FOR EACH ROW EXECUTE_PROCEDURE je_breaout();
Of course the example above is simplified. There will be a status table that will track approval status allowing for separation of duties, etc. However the goal here is to prevent unbalanced transactions.
Any feedback? Does this sound entirely insane?
Standard Solutions?
In getting to this point I have to say I have looked at four different current ERP solutions to this problems:
Represent every line item as a debit and a credit against different accounts.
Use of foreign keys against the line item table to enforce an eventual running total of 0
Use of constraint triggers in PostgreSQL
Forcing all validation here solely through the app logic.
My concerns are that #1 is pretty limiting and very hard to audit internally. It's not programmer transparent and so it strikes me as being difficult to work with in the future. The second strikes me as being very complex and required a series of contraints and foreign keys against self to make work, and therefore it strikes me as complex, hard to sort out at least in my mind, and thus hard to work with.
The fourth could be done as we force all access through stored procedures anyway and this is the most common solution (have the app total things up and throw an error otherwise). However, I think proof that a constraint is followed is superior to test cases, and so the question becomes whether this in fact generates insert anomilies rather than solving them.
If this is a solved problem it isn't the case that everyone agrees on the solution....
