I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and moreover, that solutions for them may inspire solutions for me, so don't consider this PostgreSQL-specific.
I know I am not the first to try to solve this problem so I figure it is worth asking here but I am trying to evaluate the costs of modelling accounting data such that every transaction is fundamentally balanced. The accounting data is append-only. The overall constraint (written in pseudo-code) here might look roughly like:
CREATE TABLE journal_entry (
id bigserial not null unique, --artificial candidate key
journal_type_id int references journal_type(id),
reference text, -- source document identifier, unique per journal
date_posted date not null,
PRIMARY KEY (journal_type_id, reference)
);
CREATE TABLE journal_line (
entry_id bigint references journal_entry(id),
account_id int not null references account(id),
amount numeric not null,
line_id bigserial not null unique,
CHECK ((sum(amount) over (partition by entry_id) = 0) -- this won't work
);
Obviously such a check constraint will never work. It operates per row and might check over the entire db. So it will always fail and be slow doing it.
So my question is what is the best way to model this constraint? I have basically looked at two ideas so far. Wondering if these are the only ones, or if someone has a better way (other than leave it to the app level or a stored proc).
- I could borrow a page from the accounting world's concept of the difference between a book of original entry and a book of final entry (general journal vs general ledger). In this regard I could model this as an array of journal lines attached to the journal entry, enforce the constraint on the array (in PostgreSQL terms, select sum(amount) = 0 from unnest(je.line_items). A trigger could expand and save these to a line items table, where individual column constraints could more easily be enforced, and where indexes etc could be more useful. This is the direction I am leaning.
- I could try to code a constraint trigger that would enforce this per transaction with the idea that the sum of a series of 0's will always be 0.
I am weighing these against the current approach of enforcing the logic in a stored procedure. The complexity cost is being weighed against the idea that mathematical proof of constraints are superior to unit tests. The major drawback of #1 above is that types as tuples is one of those areas in PostgreSQL where one runs into inconsistent behavior and changes in assumptions regularly and so I would even hope that behavior in this area might change over time. Designing a future safe version is not so easy.
Are there other ways to solve this problem that will scale up to millions of records in each table? Am I missing something? Is there a tradeoff I have missed?
In response to Craig's point below about versions, at a minimum, this will have to run on PostgreSQL 9.2 and higher (maybe 9.1 and higher, but probably we can go with straight 9.2).