Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

  1. Represent every line item as a debit and a credit against different accounts.
  2. Use of foreign keys against the line item table to enforce an eventual running total of 0
  3. Use of constraint triggers in PostgreSQL
  4. 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....

share|improve this question
    
For modeling standard business systems such as this one, I suggest you didn't re-invent the wheel. Take a good look at one of the several models in ERP or Universal Models that address this business requirement and use it as a basis for your model. These models are not only good, they have been proven to have worked in the real-world. Some of the links in this post may help: dba.stackexchange.com/questions/12991/… –  Emmad Kareem Sep 8 '12 at 11:16

1 Answer 1

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

share|improve this answer
    
Thanks for your response. I guess though it would be helpful if you can clarify why "such that sum(amount) = 0 when grouped by transaction id" is not a data rule but only a business rule. It seems I could express it in relational algebra even if not in SQL. –  Chris Travers Sep 8 '12 at 13:13
    
@ChrisTravers: Normalization is primarily about DML as opposed to querying. Breaking 1NF can and usually does lead to insert, update, and delete anomalies (whereas 3NF/BCNF is usually free of them). The form that you query from is actually the least of your problems; many high-performance systems use a CQRS model and don't query from a relational database at all, but 3NF helps avoid data anomalies and 2NF is also important for preventing update hotspots (frequent updates perform much more poorly than frequent inserts). –  Aaronaught Sep 8 '12 at 13:16
    
@ChrisTravers: It's a business rule because it depends on the business. The very simple rule you've defined isn't valid for every configuration. In some cases it is not restrictive enough, in other cases it seems too restrictive. DE only says that there must be at least two accounts affected by every transaction; nowhere (that I'm aware of) does it say that the sum of all these entries must be zero. –  Aaronaught Sep 8 '12 at 13:19
    
But these are all domain-specific. Again with append-only data, you don't have update or delete anomilies because you can't update or delete. The problem as I see it is that "such that sum(amount) = 0 when grouped by journal_entry_id" is a perfectly valid relational constraint, but the problem is that the constraint does not exist at the row level. In other words, with double entry, you have a class of insert anomilies which normal forms are just not designed to cope with. –  Chris Travers Sep 8 '12 at 13:19
1  
We seem to be going around in circles, so I'm going to bow out; feel free to ignore this post/advice if you believe that your model somehow improves upon all of the other existing models out there; personally, I think you'll end up regretting it. –  Aaronaught Sep 8 '12 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.