I'm looking for a nice way to build this scenario: I've got a lot of locations with some special types. For example I've got workplaces, places like real laboratories and virtual places like maybe parcel service. For each of the different types I need to store some common attributes as well as some special ones. Having OOP in mind I came to the point of inherit tables. so I've create something like that (just a minimal example):
CREATE TABLE locations( id SERIAL PRIMARY KEY, name varchar(50) ); CREATE TABLE workplaces( workers integer ) INHERITS (locations);
But now I got stuck with the primary key thing. As described in the documentation it is not supported. And now I'm looking for the best way on having at table workplaces also the unique constraint from locations etc. so e.g. I can do something like that:
What we usually do in LedgerSMB is to add an additional qualifying field (in your case, maybe call it location_class_id). This identifies the subtype and we can use it to guarantee uniqueness without resorting to various tricks. Fkeys are still a problem but a more manageable one. You can either use constraint triggers for that or fkey against a child table only where that is appropriate.
In essence I would do something like (pseudocode, untested, etc):
CREATE TABLE location_class (
id serial not null unique,
label text primary key
);
CREATE TABLE location (
id serial not null,
location_class_id int references location_class(id),
name text not null,
primary key(id, location_class_id),
check NOINHERIT (location_class_id = 1)
);
CREATE TABLE worplace (
workers int not null,
check (workers > 0),
check NOINHERIT (location_class_id = 2),
primary key(id, location_class_id)
);
That gives you a unique identifier across the tree. If you want to do away with location_class, you could make your primary key into (id, tableoid) instead but that seems too hackish to me.
Now this doesn't solve the fkey problem but it does give you uniqueness.
