This seems like a pretty common scenario: several types that all compose the same child type.
This could typically look like so:
-- 'name' is unique per parent record CREATE TABLE sometype ( sometype_id serial PRIMARY KEY, name text ); CREATE TABLE foo (foo_id serial PK); CREATE TABLE bar (bar_id serial PK); CREATE TABLE foo_sometype ( foo_id int4, sometype_id int4 ); CREATE TABLE bar_sometype ( bar_id int4, sometype_id int4 );
Which is fine, but cumbersome to query. I'm thinking this could be cleaner:
-- 'name' is unique per parent record CREATE TABLE sometype ( name text ); CREATE TABLE foo_sometype ( foo_id int4 ) INHERITS(sometype); CREATE TABLE bar_sometype ( bar_id int4, ) INHERITS(sometype);
What I like about this:
- simple to join (with
- no need to add a surrogate key to 'sometype', it is explicitly a component of 'foo' and 'bar'
Seems like an atypical use of inheritance, though.
Any reason not to do this?
On the "deficiencies" of inheritance
Note that the standard caveats to Pg inheritance are only relevant when table inheritance is used to directly model class inheritance, which is not what I am doing here. In fact, for this to work I need inheritance to behave the way it does.
I almost wish they called it something other than "inheritance", since the behavior is quite logical, and the "shortcomings" are only relevant to one use-case.
Benefits over manually duplicated table structure
As Evan points out, I could just manually create 'foo_sometype' and 'bar_sometype' that look exactly like what I describe, but I think there are a couple of significant benefits to the inherited structure:
- The inherits relationship explicitly defines 'foo_sometype' and 'bar_sometype' as being of the same type, not just two tables that happen to have the same columns.
- Making future schema changes through the parent table lessens the chance of accidental divergences (with a little work, this could actually be enforced).
- More importantly, client code can be generated against the parent table, and applied to the children with only a change in the table name, with (again) confidence that the table structure is enforced.
So, as a contrived example, Foo and Bar could have a HasSomeTypeList trait, which abstracts all 'sometype' operations, and knows that both tables can be mapped to the SomeType class.
Representing the Foo/Bar relationship, whether it's modeled as a trait or as inheritance, is the ultimate goal here.
Incidentally, to the naive user/query-writer - who is not expected to make schema changes - these two ways will look the same.