Breaking 1NF to model subset constraints. Does this sound sane?

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

“SQL-based databases” are fully capable of handling this problem without breaking normalization. Oracle has BEFORE triggers, SQL Server has INSTEAD OF triggers, and postgres has constraint triggers. All of these can query the entry table (indexed by transaction) and raise an error if the sum of entries for a specific transaction is nonzero, which satisfies the DE rule as you’ve defined it (more on this later).

Of course, it’s highly unlikely that this is actually appropriate behaviour. Double-entry bookkeeping is a constraint on the domain, whereas constraint and/or trigger systems in the DBMS are meant for constraints on the data.

Referential integrity is an actual data constraint – the data cannot be in a valid state if a row references a primary key that does not exist. Other legitimate constraints might refine a primitive data type, for example restricting a character column to ‘Y’ or ‘N’. But the balancing rule here is just a business rule. If violated, the data itself is still valid, it would just represent a bug or misconfiguration in the system that might be resolvable without data loss. Or worse, it might just represent a temporary state while some deferred business rules are being applied.

Most likely, you’ll just end up with an obnoxious, unmaintainable, poorly-performing system that is a pain to develop against.

Of course, your proposal also ignores many of the actual DE rules and thus isn’t very useful in practice even if works on a technical level, for example:

In the British system, matching entries for one type have to be made against another ledger of the same type, and for a different account. For example, you don’t debit a nominal account after crediting a personal account, and it would make no sense to credit and debit the same account for the same amount.
In the American system, entries follow the accounting equation. Matching entries don’t have to have opposite signs unless the entirety of the transaction is in the same category. A cash purchase will have positive and negative assets entries, but taking out a loan will add to both assets and liabilities.

Generally speaking, the “running total” is not going to be zero. What actually matters at the end of the day is that the different types of ledgers are all balanced according to some particular set of business rules.

I am not an accountant, and obviously you’ve spent more time working on accounting systems than I have, so maybe I’m missing something above; but as far as I can tell, this is wrong both from a technical and business perspective, so don’t do it. Accounting systems are a solved problem; please don’t reinvent the square wheel.

Filed under: softwareengineering - @ 06:26

Thẻ: database-design, postgres

Thiết kế website giá rẻ

Danh mục