Re: Table inheritance versus views

> On Fri, 2002-08-02 at 22:39, cbbrowne(at)cbbrowne(dot)com wrote:
>
>>On 29 Jul 2002 18:27:40 MDT, the world broke into rejoicing as
>>Stephen Deasey <stephen(at)bollocks(dot)net> said:
>>
>>>Curt Sampson wrote:
>>>
>>>>I'm still waiting to find out just what advantage table inheritance
>>>>offers. I've asked a couple of times here, and nobody has even
>>>>started to come up with anything.
>>>
>>>Table inheritance offers data model extensibility. New (derived) tables
>>>can be added to the system, and will work with existing code that
>>>operates on the base tables, without having to hack up all the code.
>>
>>But it kind of begs the question of why you're creating the new table in
>>the first place.
>>
>>The new table certainly _won't_ work with existing code, at least from
>>the perspective that the existing code doesn't _refer_ to that table.

Since OpenACS has been brought up in this thread, I thought I'd join the
list for a day or two and offer my perspective as the project manager.

1. Yes, we use views in our quasi-object oriented data model. They're
automatically generated when content types are built by the content
repository, for instance.

2. Yes, you can model anything you can model with PG's OO extensions
using views. If you haven't implemented some way to generate the view
automatically then a bit more work is required compared to using PG's OO
extensions.

3. The view approach requires joins on all the subtype tables. If I
declare type 'foo' then the view that returns all of foo's columns joins
on all the subtype tables, while in the PG OO case all of foo's columns
are stored in foo meaning I can get them all back with a simple query on
the table. The PG OO approach can be considerably more efficient than
the view approach, and this is important to some folks, no matter how
many appeals to authority are made to various bibles on relational
theory written by Date and Darwen.

4. The killer that makes the current implementation unusable for us is
the fact that there's no form of indexing that spans all the tables
inherited from a base type. This means there's no cheap enforcement of
uniqueness constraints across a set of object types, among other things.
Being able to inherit indexes and constraints would greatly increase
the utility of PG's OO extensions.

5. If PG's OO extensions included inheritance of indexes and
constraints, there's no doubt we'd use them in the OpenACS project,
because when researching PG we compared datamodels written in this style
vs. modelling the object relationships manually with automatically
generated views. We found the datamodel written using PG's OO
extensions not only potentially more efficient, but more readable as well.

As far as whether or not there's a significant maintenance cost
associated with keeping the existing OO stuff in PG, Tom Lane's voice is
authorative while, when it comes to PG internals, Curt Sampson doesn't
know squat.

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org