Best practice on inherited tables

Just our experience in LedgerSMB....

On Fri, May 17, 2013 at 5:46 AM, Frank Lanitz <frank@frank.uvena.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

Hi Frank

Although you are thinking in OOP, the SQL is itself one definition model that you should not ignore and, IMHO, try to follow the normalization statements.

You can build a robust and normalized schema (table primarylocation , table secondlocation that have a idprimarylocation, etc.) and later you could build your OOP software translating this to the proper classes (in Java you could use a DAO-POJO class or hibernate-jpa, for example).

With this solution you can obtain all the benefits of DBRMS besides a OOP robust software :)

Best regards

El 17/05/2013, a las 13:46, Frank Lanitz <frank@frank.uvena.de> escribió:

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:

INSERT INTO workplaces (name, workers) VALUES ('My Place', 5);

having the incrementation and the uniqueness. I was thinking off
creating a number of triggers doing this for me but wondering whether
there might be a better way.

Cheers,
Frank

BTW: Using Postgres 9.2 and up

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alfonso Afonso
(personal)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 17/05/13 22:46, Frank Lanitz 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);

Hi,
Wouldn't you prefer something like locations, workers, worker_locations
table schemas?
INHERITS is a feature, but you have to be careful and differentiate for
when it is actually useful. It can bring up debate involving OOP
practices transfered over into general database design. For which I
quash with:

database design != application design

How you store data should be irrelevant to application interfaces
(API's). People do it and some frameworks encourage it.

Regards,
Julian.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In general, I find table inheritance most helpful when one wants to re-use
interfaces over multiple independent tables. For example instead of a
global notes table, a bunch of notes tables attached to things, but with
centralized schema management. In general in these cases you want
something identifiably unique across the entire tree (which is why tableoid
or a *_class_id field would be added).

For example in the case at hand, inheritance would not be a great tool if
you wanted to have a lot of foreign keys against the locations tree.
However if you wanted to store locations with geospacial data, and then
have a bunch of separate location tables (as full tables in their own
right), this would be fine. I think further that the foreign key issue is
going to need some attention at some point because of the way table
inheritance is used in table partitioning.

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.

I have spent some time blogging about this issue. I don't think the fact
that these are different necessarily means that Object-relational modelling
in the db is not extremely useful.

Best Wishes,
Chris Travers

Am 17.05.2013 21:21, schrieb Alfonso Afonso:

Hi Frank

Although you are thinking in OOP, the SQL is itself one definition
model that you should not ignore and, IMHO, try to follow the
normalization statements.

You can build a robust and normalized schema (table primarylocation ,
table secondlocation that have a idprimarylocation, etc.) and later
you could build your OOP software translating this to the proper
classes (in Java you could use a DAO-POJO class or hibernate-jpa, for
example).

With this solution you can obtain all the benefits of DBRMS besides a
OOP robust software :)

I was really thinking about this way as it's tradition relational model
and of course kind of a rock stable solution. But I have the fear that
it will end up in a real mess of joins at database layer in the end so I
thought to make usage of such a feature if available ;)

Cheers,
Frank

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).

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

The principles of OOP are to be brought into database design with risk.
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.

Regards,
Julian.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, May 19, 2013 at 4:44 AM, Julian <tempura@internode.on.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