Re: Imlementing super/subtype logical model

Bill,

>I need help on the best way to implement supertype/subtype relations in
>Oracle.

There are four options in general to implement a super/subtype situation:

1. One entity for the super and one entity for each sub
2. One entity for each sub with the superinformation in the subs
3. One entity for the super and one for all subs together
4. One entity with all information.

The choice for one of the possible solutions depends on a number of facts. Choose for

a, when you want a flexible situation for the future or if super and subs all have more than a few attributes

b, when the super doesn't have much attributes and the subs do

c, when the super has information that will be retrieved (a lot) more often than the subs or when the super has more than a few attributes and subs all together also

d, when there are few attributes in as well super as subs

This is a rough selection. Of course, the amount of activities on each entity will be very important for the choice (ie option c).

The integrity of the database will have to be garanteed with triggers for all options but the first. In general, I would choose for option A.

>How do I structure a query to list the hiring dates of all employees?

For a single entity:

select *
from employee
order by hiredate

For a super/sub solution with a choice for option a:

select *
from persontype
, employee
where persontype.type = employee.type
order by hiredate

>This is probably simple, but I've managed to make it complicated!

How?

• Martin Jesterhoudt
• martinj(replace this by _at_)xs4all.nl

  ---