Re: Question on "best practise" for SELECTS on inherited tables

On Tue, Dec 8, 2009 at 10:30 AM, Hubertus Freiherr von F?uerstenberg
<hubertus(dot)fuerstenberg(at)ise(dot)fraunhofer(dot)de> wrote:
>
> please consider the following exemplary setup:
>
> I want to store information on people in a database. People can be either
> internal (staff) or external (company contacts, etc.). The idea was to use
> one table for all people and have the tables that store specific information
> inherit from it.
>
> CREATE TABLE people (
>    id SERIAL PRIMARY KEY,
>    name VARCHAR(128),
>    given_name VARCHAR(128),
>    internal BOOLEAN DEFAULT TRUE
> );
>
> CREATE TABLE internal_people (
>    uid VARCHAR(8),
>    role VARCHAR(32)
> ) INHERITS (people);
>
> CREATE TABLE external_people (
>    company INTEGER REFERENCES companies(id)
> );
>
> What would be the best way to select a person from table people and
> depending  on "internal" have the information from internal_people or
> external_people displayed as well?

I would personally not advise the use of the build in inheritance
feature for anything but table partitioning strategies as described in
the documentation. To do inheritance, I'd stick with a tried and true
relational approach (there's several methods, here's one):

CREATE TABLE person_type(type text);
INSERT INTO person_type values('INTERNAL', 'EXTERNAL'); -- this is
overkill for just two types

CREATE TABLE people (
people_id SERIAL PRIMARY KEY,
name VARCHAR(128),
type text REFERENCES person_type,
given_name VARCHAR(128),
internal BOOLEAN DEFAULT TRUE
);

CREATE TABLE people_internal ( -- like this naming better
people_id INT PRIMARY KEY references people ON DELETE CASCADE,
[...] -- internal specific fields
);

and so forth. you can even create a view:

SELECT p.*,
case when p.type = 'INTERNAL' then pi::text when p.type = 'EXTERNAL'
then pe::text end as details,
from people p
left join person_internal pi on p.type = 'INTERNAL' and p.id = pi.id
left join person_external pe on p.type = 'EXTERNAL' and p.id = pe.id;

Then you get a single flat listing of people with all inherited
details globbed together in text composite notation that can be casted
back to the actual person type you want later...IMO, this solution
works very well. What you really want, which is to have a table with
a variable set of columns depending on type, does not exist in
postgresql today. The inheritance feature aimed for it, and
unfortunately missed.

merlin