Jim wrote:

> I proposed an alternative solution in which a base table containing,
> among other stuff, the fields last_update_date and last_update_user
> and the triggers to maintain those fields. Other tables could then
> "extend" the base table, inheriting both the fields and the triggers.

I definitely prefer some form of table inheritance over trigger templates. I
think it's a much cleaner and more powerful design that supports OO
application design more closely.

> Table inheritance, however, raises all sorts of interesting questions
> that beg answers before we can even start arguing about implementation
> stategy. Nice, thick, yummy sort of questions.
>
> Question 1: Does table inheritance simply mean attribute (fields,
> triggers, primary keys, indexes) inheritance, or does it mean a
> a unified semantic model? We could, of course, have both, but
> for the sake of elegance, lets assume one or the other.
>
> create table base (last_update_date, last_update_user)
> create trigger for base ...
> create table products (product_number...) extends base;
> create table customers (customer_number...) extends base;
> insert into products (product_number) values (123)
> insert into customers (customer_number) values (456)
>
> Simple inheritance:
> select count (*) from base
> 0
>
> Semantic model:
> select count (*) from base
> 2

Personally, I'd really like to have the full semantic model since it nicely
handles cases where you have a base entity (say ledger account) and various
derived entities (bank account, debtor account), etc. which have their own
additional fields. We want to be able to work with all legder accounts at
one level but also deal with just bank accounts at other levels.

Now, we were faking a semantic model by having of 1:1 joins from the bank
account table into the ledger account table, etc. It works, but it's fiddly
and involves a fair bit of mucking around on the client side to make all the
1:1 joined tables 'behave' as a single unified query.

If the engine supported a full semantic model, that would all go away and
make OO -> relational mapping a whole lot easier.

I'm not say IB shouldn't have the option of simple inheritance but I'd love
to have the full semantic model for data models that wanted it.

> Question 2: Are primary key inherited? Choices: a) yes, b) no,
> c) declared primary key of extension table appended to primary
> key of base table (problem is that uniqueness of base table is
> problematic).

Hrm... which primary key? In our ledger model, we were using surrogate (i.e.
generated) keys for all the tables to support the various joins (both 1:1
and 1:M) with a second user-visible unique key (which could be changed) in
the UI. I guess this is more of an issue for the full semantic model over
simple inheritance since base table instances will be created with each
derived instance to there is more potential for key conflicts.

> Question 3: Is multiple inheritence necessary? Most language
> designers would say that multiple inheritence is mother natures
> way of telling you that your design is screwed up. Multiple
> inheritence in C++ is moby complicated and usually does the
> wrong thing. Java dump it in favor of single inheritance
> plus implementation of interfaces (polymorphism without
> representation). What is the minimal feature set we can live
> with?

Interfaces I like, multiple inheritance I've found to be tricky since
inheriting implementations (as opposed to the interface) seems fraught with
subtle bugs. It seems to me that much of table design _is_ interface (i.e. a
schema) but inheriting triggers is bordering on implementation stuff which
is where things start to get tricky. Unless it was possible for triggers to
'override' base class triggers and extend and/or call into their parent
trigger to catch the MI cases, it would be pretty awkward methinks.

TTFN,
Paul.