Thread: Best practice on inherited tables
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
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
followthe 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
coulduse 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)
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.
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
Attachment
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.
On Sun, May 19, 2013 at 4:44 AM, Julian <tempura@internode.on.net> wrote:
On 19/05/13 13:02, Chris Travers wrote:INHERIT is feature that immediately gets interest from an OOP
>
> 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.
>
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