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

3 thoughts on “Pagila Sample Database

  1. Hi Pasha, I’m glad to see you have found Pagila as useful, and very glad to see it being included with the next Data Designer.🙂

    With regards to the above int vs smallint; I’m not sure much thought was ever given to it. One of the original tenets of the schema was that our goal was to show off different capabilities of Postgres, rather than try and show the “correct” way to design schema. On the other hand, this does seem like more of a mis-feature than a feature, so I could see changing it.

    Like

  2. Hello.

    Just letting you know that we have ported Sakila Sample database to Firebird,Oracle, SQLite, SQL Server and Access.
    Fell free to use as a sample database
    Please let us know if we can improve it somehow

    http://code.google.com/p/sakila-sample-database-ports/

    About the project

    Sakila example database was originally developed by Mike Hillyer of the MySQL AB documentation team.

    This project is designed to help database administrators to decide which database to use for development of new products The user can run the same SQL against different kind of databases and compare the performance

    Mike,
    DB Software Labopratory

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s