Re: Inheritance - Mailing list pgsql-hackers

On Fri, 2002-09-06 at 19:00, elein wrote:
> 
> 
> There was a comment earlier that was not really addressed.
> What can you do with table inheritance that you can not do
> with a relational implementation?  Or what would work *better*
> as inheritance?  (you define better)

There is nothing that you cannot do in some way; that way may not be
very convenient compared to the use of inheritance.  I consider
simplicity to be preferable to conceptual purity.

> This is a genuine question, not a snarky comment.  I really
> want to know.  This is the reason I can think of to use
> inheritance: Several tables have a common set of attributes and
> there is some reason for these tables to be separate AND there
> is some reason for the common columns to be queried en masse.
> What kinds of "some reasons" are there, though?  And if my
> condition for using table inheritance is lacking or misguided, what should
> be the criteria for using table inheritance?

I use it when a group of tables are closely related; they are all
members of some higher class.  For example:
                              person <.......................> address                                 |
 +--------------+--------------+                  |                             |            organisation
   individual <......> pay_tax                  |                             |         +--------+--------+
+---------+---------+        |        |        |          |         |         |    customer  supplier ...etc...   staff
homeworker ...etc...         |    +----+-------------+    |                  |
 
home_customer    export_customer

It is convenient to use a higher class when you are interested in all
its members and only in the attributes of the higher class.  So I can
say
  SELECT * FROM person,address   WHERE address.person = person.id AND         address.town = 'London';

to get all rows for people in London.  I will only get those attributes
that are in person itself; if I want to know about credit limits, that
is only relevant in the customer hierarchy and I have to SELECT from
customer instead..

Similarly, I can use the whole customer hierarchy when changing or
reporting on outstanding customer balances.

If foreign key relations were valid against an inheritance tree, I could
implement it for a table of addresses referencing the highest level
(every person has an address) and of pay and tax records at the
individual level.  These don't change as you go down the hierarchy, but
a purely relational implementation has to be redone at each level.  A
reciprocal relation requires an extra table to hold all the hierarchy's
keys and that in turn needs triggers to keep that table maintained.
(I.e., person should have a FK reference to address and address to
person; instead, address needs a reference to person_keys, which I have
to create because FK against a hierarchy isn't valid.)  The lack of
inherited RI makes the design more complex and more difficult to
understand.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "For whosoever shall call upon the name of the Lord      shall be saved."
    Romans 10:13