Gadgetopia

PostgreSQL 8: Inheritance: Joseph Scott — a champion of PostgreSQL — pointed this little feature out in a comment to my post on the relational data model.

It’s a method in PostgreSQL of subclassing tables. You can create a table that would have a one-to-one relationship with a “parent” table if the relationship was done manually via foreign keys. But Postgres maintains this relationship internally with no visible reference in either table to the other table — it’s like creating a view for each parent-child instance.

Here’s the example from the page:

CREATE TABLE cities (  
name            text,  
population      float,  
altitude        int     -- (in ft)  
);  

CREATE TABLE capitals (  
state           char(2)  
) INHERITS (cities);

So I can insert stuff into “capitals,” referencing fields in “cities.” “Capitals” will contain all the fields that “cities” does, plus its own fields — it’s a subclass, automatically represented in SQL and the underlying table structure.

I’ve heard that DB2 does this too. It seems like it’d be very nice and save lots of code. This makes me want to create my object model in stored procs instead of PHP or whatever. Perhaps that’s unworkable in practice, but it would be more elegant from a data perspective.