At 09:15 AM 7/18/00 +1200, Paul Heinz wrote:

>Jim wrote:
>
>>
>> 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.
>

I neglected to attach the babel fish to my original post. The
implementation I'm suggesting would implemented the example above
like this:


create table physical_table (instance_type, last_update_date,
last_update_user, product_number, customer_number...)

create view base as select last_update_date, last_update_user
from physical_table

create view products as select (last_update_date, last_update_user,
product_number...)
from physical_table
where instance_type = 'PRODUCTS'

create view customers as select (last_update_date, last_update_user,
customer_number...)
from physical_table
where instance_type = 'CUSTOMERS'

With appropriate triggers this can be implemented outside the engine.
Easier to hack the system tables with everything inside (probably).

>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.
>

If we do, we'll do it right.

>
>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.
>

I think you understand the problem

Jim Starkey