Re: inheritance

Dmitry,

Dmitry Tkach wrote:

> You are right - a complicated and hairy 'inheritance' structure,
> emulated with views will result in some inefficiencies (although 'a very
> large master table' is not a problem at all - it is generally better
> from peformance standpoint to have one large table than many smaller
> ones, especially when those smaller tables duplicate the content of each
> other, as it is the case with inheritance)...


Final extension of this idea would be the true OO methodology of having
a single relation from whence all others are untimatelly inherited.  Not
an idea I would use, but has advantages.  In this case, using VIEW
method, entire database would be just one table.  Are you saying this is
better on performace?

Real advantages I can personally use today:  We have many relations with
common index structure, some common fields and some common foreign keys.
  The ability to inherite all these from a template relation is very
useful...  Even if parent table is never used.

> But the point is that implementing the same complicated and hairy
> structure using the "real inheritance" will actually result in the same
> (if not worse) performance problems (you'll end up having all those
> tables, sitting around, that need to be sync'd with triggers after every
> statement you make) - only those problems will be much harder to spot,
> because of lots of stuff going on behind the scenes ...
>
> For example,  the simplest possible query like 'select * from
> the_superclass_table' will get translated into something like...
> select * from the_superclass_table union all
> select ... from the_first_subclass union all
> select ... from another_subclass union all
> select ... from subclass_of_the_first_subclass union all
> ... etc, etc, etc...

I do not know the methods used with Inheritance.  Whether the data is
duplicated through the ancestoral structure, or referenced.  (Would be a
nice configurable option?)  If your saying all data in superclass_table
is only UNION'd at run time, then I totally agree, big performace hit!


> The point is, that if you avoid the "object-oriented" style, you'll see
> (most of) these problems right away, when designing your schema, and
> will be able to avoid them, by restructuring your data, and optimizing
> your schema for performance.


Certainly.  But there are places where an OO methodology does have magor
advantages in Relational Theory, just as it does in C / C++ coding.
Abstraction and Polymophism being the biggest I can see from postings on
this subject.  Both of which can be difficult in traditional Relation
Theory...  But I look forward to some comments on this subject :)


> P.S. BTW, you mentioned foreign keys... They don't work with inheritance
> either (just like unique constraints don't - see my earlier message)
> For example:
>
> create table a (id int primary key, name text);
> create table b (last_name text) inherits (a);
> create table c (id int references (a), data text);
>
> insert into a values (1, 'dima');
> insert into b values (2, 'dima', 'tkach');
>
> insert into c values (1, 'This works');
> insert into c values (2, 'BUT THIS DOES NOT!');
>
> ERROR:  $1 referential integrity violation - key (id)=(2) referenced
> from c not found in a

Surelly this is correct and expected?  the 'DOES NOT!' value refereces a
value which truelly does not exist.  It's not in the 'c' relation, only
in it's parent 'a'.  Surelly the idea of inheritence is that parents
have access to child data, not the other way round ??

Ben