Pagila Sample Database

I asked myself once, what would be a worthy DB for PostgreSQL which I could use to test server functionality, and I wanted to have some test environment. Finding my answer turned out to be not an easy task to solve by brute force.

Frankly speaking, it is easier to find PgFoundry site using Google than finding the right link at official PostgreSQL site. For the ones searching – here sample DBs are stored.

So, examined the contents I found that for me the most attractive DB would be Pagila.

There is one thing actually that disturbing me. What did made developers create 20 foreign keys in which int2 type columns referenced on int4 type columns? To be clear:

CREATE TABLE "country" (
	"country_id" SERIAL,  /* <<<<<<----- */
	"country" varchar(50) NOT NULL,
	"last_update" timestamp NOT NULL DEFAULT now(),
  CONSTRAINT "country_pkey" PRIMARY KEY("country_id")
);

CREATE TABLE "city" (
	"city_id" SERIAL,
	"city" varchar(50) NOT NULL,
	"country_id" int2 NOT NULL, /* <<<<<<----- */
	"last_update" timestamp NOT NULL DEFAULT now(),
  CONSTRAINT "city_pkey" PRIMARY KEY("city_id"),
  CONSTRAINT "city_country_id_fkey" FOREIGN KEY ("country_id") /* <<<<<<----- */
    REFERENCES "country"("country_id")
	MATCH SIMPLE
	ON DELETE RESTRICT
	ON UPDATE CASCADE
	NOT DEFERRABLE
);

I know you can do this by means of PostgreSQL. You can do even more sofisticated connections, as long as there is an operator capable of comparing two types in foreign key. But why do this in 20 out of 22 constraints? – that’s the real question.

A bit of statistics since we’ve started about it:
Domains: 1
Tables: 15 (+ 6 partitioned)
Foreign Keys: 22
Primary Keys: 15
Indices: 32
Stored routines: 10
Views: 7

Anyway, I like Pagila’s structure so much that next MicroOLAP Database Designer release will contain Pagila in two variations: light & full. Light version won’t have table partitioning for “payment” table.

That’s it. 🙂