Re: Best practice on inherited tables

On Sun, May 19, 2013 at 4:44 AM, Julian <tempura(at)internode(dot)on(dot)net> wrote:

> On 19/05/13 13:02, Chris Travers wrote:
> >
> > I actually think that bringing some object-oriented principles into
> > database design can result in some very useful things, provided that one
> > remembers that applications are modelling behavior while databases are
> > modelling information (and so the two behave quite differently). The OO
> > principles are helpful particularly when looking at derived data.
> >
>
> INHERIT is feature that immediately gets interest from an OOP
> perspective, however its usefulness, IMO, is naught. I agree that
> applications model behavior (do this) but databases don't model
> information, their core purpose is to store information in such a away
> that the information may be modeled how you see fit, using a query
> language (SQL).
>

I actually find table inheritance quite useful in a fair number of cases.
Here are some examples where it is obviously useful:

1. In LedgerSMB we use it to avoid a global notes table by using the notes
table as an abstract (no rows) table which exists only to create consistent
interfaces for notes that would attach elsewhere. So this enables us to do
soemthing like:

CREATE TABLE note (
id serial,
subject text,
note_class_id int references note_class(id),
content text,
ref_key int
);

CREATE TABLE invoice_note (
primary key (id, note_class_id),
unique(id),
ref_key references transactions(id)
) inherits (note);

We currently have I think 5 tables which inherit note in this way, and this
avoids the headaches associated with a global notes table.

2. Useful in table composition.

I have an internal application which is designed with some fairly wide
tables because of relatively flat functional dependencies. These are
transitive dependencies, but there are dependencies across different
groups, so I can define a "normalized" schema, and then compose
denormalized tables through inheritance. This makes breaks wide tables
into digestable chunks, and it provides interfaces specific to those chunks.

3. Useful with "table methods" for consistent interfaces. For example, we
could do something like:

CREATE OR REPLACE FUNCTION tsvector(note) RETURNS tsvector AS
$$
SELECT to_tsvector($1.subject || ' ' || $1.content);
$$ language sql;

Then we can:

SELECT ... FROM invoice_note WHERE note.tsvector @@
plainto_tsquery('looking for something');

>
> Storing data to how you wish to receive it, is not realizing the true
> power of a RDBMS like postgres.
>

No, that's not the point.

The point is in the ability to store data so that interesting information
can be derived from what you have stored. It is often also decoupled
entirely from app design. There are certain kinds of problems where you
can use inheritance to safely do things with data modelling in simple ways
which would take a great deal of complexity to do without it. The notes
example is a good example. Without inheritance you could either have a
bunch of mapping tables (in which case you can't verify that each note is
attached to only one thing), or you can add a bazillion foreign keys to the
table. Now what you lose is a clean way to do foreign keys into the notes
table and include subtables, but in this case, that really doesn't matter.

>
> The principles of OOP are to be brought into database design with risk.
>

Sure. One wants to master relational design first.

> INHERITS actual usefulness is merely an alternative to already accepted
> relational database principles and actually goes *against* the
> relational model. Postgres has alot of features. So even without the
> current caveats I warn against using INHERIT when I see it mentioned.
>

Not always. Let me give you a simple contrived example.

CREATE TABLE foo (bar int not null, baz text not null);
CREATE TABLE foobar (foo foo, comment text, check((foo).bar is not null and
(foo).baz is not null);
INSERT INTO foobar (foo, comment) values (row(1, 'testing')::foo, 'this is
a test of nested tuple structures);

Note that example (nested tuple structures) doesn't actually violate the
relational model, and it does not violate first normal forms atomicity
requirement (since foo is a single value of a domain, it no more violates
atomicity than datetime does). Now, check constraints are a little more
complex in this model but it does work. For example, not null constraints
on foo do not cascade to foobar.foo.

With table inheritance however, we can actually handle this better:

CREATE TABLE foobar (comment text) inherits (foo);

Now the not null constraints properly cascade but instead of SELECT (foo).*
from foobar; we just do:
SELECT (f::foo).* from foobar f;

Now this doesn't really violate the relational model any more than the
first one does. We might use a base table for a group of commonly occuring
fields which have a similar meaning but occur in which the functional
dependencies from these columns on out are variable.

The key thing though is that the discussion on inheritance needs to stay at
the database level. One shouldn't be modelling database data storage
structures based on application structures (there we are in total
agreement).

Best Wishes,
Chris Travers