Jim wrote:

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

<snip>

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

Interesting... We did something much like your above example in our ledger
model as well.

However, instance_type was a field called ClassID (which was an integer) and
we also had a field called ObjectID (another integer) which was generated by
an appropriate generator based on the ClassID. So in our model, ClassID +
ObjectID was the unique primary key for our base table.

In fact, we further leveraged this by registering ClassIDs in our
application so that we could instantiate a data object (and form view) based
on the ClassID which would pull in the appropriate data based on the
ObjectID.

This allowed us to stored in our ledger journals table a SourceClassID and a
SourceObjectID which indicated the 'source document' object that produced
the journal. Really nice for drilling down in your ledger and ending up back
on the invoice line which 'produced' the journal. And we could add new
ClassIDs for new document types as the application grew additional
subsystems i.e. Debtors, Creditors, Stock. In fact, these ClassID+ObjectID
pairs were really handy for easily implementing tracking of workflow
produced from various documents through the accounting system.

So in the full semantic model, perhaps some form of engine managed ClassID
analogue stored for each row would be useful to help resolve the primary key
issues and ideally, it would be something that was 'visible' outside the
engine to be leveraged by applications.

TTFN,
Paul.