Denormalizing the real world to normalize your database

When writing database schema's sometimes we envision a 1-to-1 correlation with what we are trying to model from the real world and assume it must be a good design because it seems to fit so perfectly. This is not always, and perhaps not even often, the case.

Double Entry Ledger

What do I mean by this? Let's use an accounting ledger as an example. It looks like a nice clean table on paper. Columns and rows that translate nicely to a database table. There is also a well defined process for making entries called double entry bookkeeping. You insert one entry for the debit and one for the credit. They must both be entered together in one transaction. They even call these transactions in accounting! Seems pretty cut and dry, right?

 tx_id |            tx_tsz             | acct_id | debit | credit | tx_type |    ref     |       dscr       
-------+-------------------------------+---------+-------+--------+---------+------------+-------------------
     1 | 2012-04-30 17:25:58.003826-04 |       1 |   100 |        |       1 | order_id 1 | Sale to Alice
     1 | 2012-04-30 17:25:58.003826-04 |       2 |       |    100 |       1 | order_id 1 | Sale to Alice
     2 | 2012-04-30 17:31:58.953455-04 |       2 |   100 |        |       2 | order_id 1 | Refund from Alice
     2 | 2012-04-30 17:31:58.953455-04 |       1 |       |    100 |       2 | order_id 1 | Refund from Alice

The problem is this whole system is designed for a human to enter the data manually using pen or pencil on paper. It was never meant to scale. One of it's main benefits being error checking for error prone humans. You make separate entries for both debits and credits. Then you add both columns up. They should be identical. If they aren't, you made a mistake in the entry, or in the summing. This is how you keep your books "balanced."

Computers on the other hand don't make mistakes when programmed properly, and when programmed improperly often make the same mistake repeatedly. So if it made an error on the debit entry it likely made the same error on the credit entry. Or if it made an error summing the debits it most likely made the same error summing the credits. If you have an actual human making ledger entries into a computer and you want to have them use double entry, you probably should be doing the error checking in your interface instead. For your cosmic ray data corruption, there are usually better methods for error checking than this.

Implementing strict double entry accounting in a database limits the benefits and introduces new problems like requiring twice as many rows in a table. You are also "linking" the rows by a "unique" transaction ID. Except it's not unique since you are using it in two rows. This means you can't put a unique constraint on it and thus you are limiting the benefits of putting it in a database in the first place. Starting to sound like the worst of both worlds.

You might even have had accountants tell you that you should store it this way in the name of best practices. What they mean is that you should be able to retrieve it back out of the system in that format. In their world presentation and implementation are probably the same thing. I am sure that your accountant is fine with (if they are even aware of) the fact that all the data is stored in binary and that the data is spread across the disk based on the inode allocation heuristics of your file system. They also aren't complaining if you are compressing the data. Why would they really care if you are normalizing it then? All they really care about is that you don't lose any information and that you can present it to them in a format they are comfortable with. You could implement this with a view or perhaps just in your reporting package.

Here's what I would propose:

 tx_id |            tx_tsz             | debit_acct_id | credit_acct_id | amt | tx_type
-------+-------------------------------+---------------+----------------+-----+---------
     1 | 2012-04-30 17:25:58.003826-04 |             1 |              2 | 100 |       1
     2 | 2012-04-30 17:31:58.953455-04 |             2 |              1 | 100 |       2

This is the minimal amount of information you would need. It would be trivial to create a view based on this table that mimics double entry accounting. You still have a debit and a credit account. This keeps the feature of double entry accounting that you can track where the money came from and where it is going. You may also notice that 'ref' and 'dscr' are gone. I would replace 'ref' with a real relational table. One that is basically two foreign keys to the ledger and the other table, in this case an orders table. I would also argue that 'dscr' (short for description) would be better served as a separate table with a foreign key as well. Most times I see this column as redundant data. In the few cases when it's not, for example it contains a short note from someone who did an override, I think it would be best to keep that external to the ledger. This way you don't have a lot of useless information or NULL's. If you have a good use case for a column of that type, by all means add it. Also I used integers for the tx_id and *_acct_id's. You may want to use UUID's for one or all of these. If you do, be sure to use Postgres' awesome UUID type.

For the record I have seen a denormalized ledger in the wild several times. That's why I decided it was worth blogging about. I don't think people are dumb if they have implemented it that way. It's very easy to find something in another discipline to use as a model for something in a database, but sometimes it's just sub-optimal and will cause you problems down the road when your ledger is 2-4 times as large as your next largest table and it's becoming unwieldy. It's a pretty important table to a business so I just want to see people not fall into the easy to avoid traps.

Deploy Schemata Like a Boss

One of the many new features in Postgres 9.1 is Extensions. In their simplest form they are a collection of database objects. Think of it as package management for your database. It lets you add, remove and upgrade a collection of objects. All contrib modules shipped with Postgres now use this method.

This is just the beginning though. Normally when you think of contrib modules for postgres you think of some new functions or data types. With extensions you can just as easily manage relations. Create a table in one version of the extension and then add a column in another version. Be sure which versions of your functions and relations exist on a database by managing them as a group instead of as individuals. You can break up your schema into logical chunks and give responsibility of each chunk to an appropriate team in your development group to distribute the workload.


One extension usually has multiple versions. The version identifiers don't need to be numeric. It doesn't understand the concept that version 2 might be newer than version 1. So rather than upgrading, you are merely transitioning between versions. You can think of this like a directed graph. Each node is a version and each edge is an update path. You can also chain paths if there is no direct route. In the example to the right, there is no direct path from β → γ but you can go β → δ → γ. Below you can see all the permutations listed from the database.

example=# SELECT * FROM pg_extension_update_paths('myext') ORDER BY source,target;
 source | target |    path  
--------+--------+------------
 α      | β      | α--β
 α      | γ      | α--δ--γ
 α      | δ      | α--δ
 β      | α      | β--δ--γ--α
 β      | γ      | β--δ--γ
 β      | δ      | β--δ
 γ      | α      | γ--α
 γ      | β      | γ--β
 γ      | δ      | γ--α--δ
 δ      | α      | δ--γ--α
 δ      | β      | δ--γ--β
 δ      | γ      | δ--γ
(12 rows)

There is also a special source called 'unpackaged' that you can use to take an existing schema and put it into an extension. It is simply a script that takes every object in your database and adds it to your extension. This way you don't have to do a big migration to get your schema working out of an extension.

So you've decided you want to try this? Great! Here is what you will need:

A control file (<extname>.control)

default_version = 'β'
comment = 'my extension'
relocatable = true

default_version is the version that will be installed unless otherwise specified. relocatable means that it can be put in an arbitrarily named schema and also move from one schema to another. The only reason that wouldn't be true is if you explicitly reference a schema name in your scripts. There are some other settings you can use as well, such as requires which adds a dependency to another extension. For example if your extension requires the pgcrypto extension.

A Makefile:

EXTENSION = myext
DATA = myext--α.sql myext--α--β.sql myext--α--δ.sql myext--β.sql myext--β--δ.sql myext--γ.sql myext--γ--α.sql myext--γ--β.sql myext--δ.sql myext--δ--γ.sql
PG_CONFIG = /usr/pgsql-9.1/bin/pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

EXTENSION is the name of your extension. DATA is the list of all the install and update SQL scripts. Every time you add a new version you need to append to this line. PG_CONFIG is the path to your pg_config binary for the install you want to use. The last two lines are just standard Postgres Makefile code for pulling in all the install specific information it needs.

The other pieces you need are the SQL scripts to do all the work. The install scripts (<extname>--<version>.sql) are basically just a bunch of create statements. Something like you would get from a pg_dump. They create all the objects for a particular version. The update scripts (<extname>--<source>--<target>.sql) consist of a combination of create, alter and drop statements. This is all the SQL that you would need to modify a schema of the source version to convert it to a schema of the target version. This will usually be a create table or alter table or a create or replace function. If you need to remove an object you will first need to disassociate it from the extension.

ALTER EXTENSION myext DROP TABLE foo;

Then you can run a standard drop table. If you want to be able to "update" to the previous version, you will need a separate script for that as well. I would suggest not trying to make a graph as complex as the one above. Most likely yours will be completely linear.

So how easy is it to install the extension? Run make install in your extension directory, then:

example=# CREATE SCHEMA myext;
CREATE SCHEMA
example=# CREATE EXTENSION myext WITH SCHEMA myext;
CREATE EXTENSION

That easy. And to update?

example=# ALTER EXTENSION myext UPDATE TO 'γ';
ALTER EXTENSION

You can use \dx to list installed extensions:

example=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description        
---------+---------+------------+------------------------------
 myext   | γ       | myext      | my extension
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

As you can see from this brief introduction this is pretty powerful stuff. It's still a little rough around the edges, but it will be getting better. I recommend taking a look at the documentation to get a more complete understanding of all this can do.