Re: Best practice on inherited tables

Just our experience in LedgerSMB....

On Fri, May 17, 2013 at 5:46 AM, Frank Lanitz <frank(at)frank(dot)uvena(dot)de> wrote:

> Hi folkes,
>
> 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.

Best Wishes,
Chris Travers