Subject Re: [firebird-support]Semantic Data Model was Derived tables
Author unordained
There's overlap between table inheritance and derived tables (views): using
views to achieve table inheritance.

create table entities (tag varchar(100), field1, ... fieldN);
create view people as select * from entities where tag = 'person';
create view taxables as select * from entities where tag in ('person',
create view fireable as select * from entities where field17 = 'bob';

You can achieve multiple-inheritance that way (reuse fields across different
views), and you can use CHECK constraints to keep things sane. You can get a
form of "lazy inheritance", where the type of a record isn't predetermined
(fireable, above); it's akin to the inheritance problem of "are circles
ellipses?" [c.f. C.J. Date, Third Manifesto]:

create table ellipses (id, long_axis, short_axis);
create view circles as select * from ellipses where long_axis = short_axis;

Firebird supports the WITH CHECK OPTION clause on views, so you can't modify a
circle so it becomes an ellipse, at least not through the "circles" view.

Firebird won't automagically figure out how to insert records into an inherited
table like the above ('tag' might not be shown, and even if it is, do you
really want someone to *set* it, when it should be obvious from the view being
used?) but insert/update triggers on the view can help out. (That won't help
when inserting into 'taxables', above, where you must specify what you want the
outcome to be -- is it a taxable person, or taxable company?)

Firebird doesn't support foreign keys pointed at views. You can, to some
degree, get around that:

alter table entities add is_employer char(1) default '0' not null;
create trigger entities_is_employer for entities before insert or update as
new.is_employer = case when tag in ('person', 'company') then '1' else '0' end;
alter table entities add employed_by integer;
alter table entities add require_employer char(1) default '1' not null check
(value = '1');
alter table entities add constraint entity_employed_company foreign key
(employed_by, require_employer) references entities (id, is_employer);

AFAIK, you can't use computed-by fields for the above, even though you can put
an index on them (has something to do with the triggering mechanism that
verifies FK's?), thus the use of a trigger & check constraint. (as opposed to
the more convenient "computed by (1)".)

[Haven't verified any of my syntax, sorry.]
[Also, whoever asked may only have cared about making this easier, not finding
a solution for "today" -- sorry.]