Re: How to implement SubType/SuperType in Oracle ?

xtanto_at_hotmail.com wrote:
>
> I have a supertype and three subtypes, as attached below, how is the
> best approach to implement it in Oracle (9i/10g) ?
>
> Supertype : Employee
> Employee_Number
> Employee_Name
> Address
> Employee_Type
> Date_Hired
>
> SubType : Hourly_Employee
> Hourly_Rate
>
> SubType : Salaried_Employee
> Annual_Salary
> Stock_Options
>
> SubType : CONSULTANT
> Contract_Number
> Billing_Rate

The best approach depends on what your requirements are. If this is a Relational Design, then simply apply Codd's normalisation rules and normalise these entities to 3rd normal form.

If you want an Object Relation (O-R) design, it is a tad more complex as the objects are persist as rows or columns in a table. A single column itself can be a collection of persistant objects (called a nested table in Oracle speak). There are limitations in dealing with nested tables from a query perspective as each column contains a distinctly unique table - you cannot simply query all nested tables. After all, each nested table (collection) has no relationship with another object's collection.

O-R designs can be very flexible - but otoh relational designs and theory are mature, principles easy to grasp, understood by many more than object designs.. so I tend to favour relational designs myself (seeing how a muck up many developers already make using relational designs I think O-R designs are too complex for the ordinary developer).

So why O-R in Oracle? It has a terrific application in PL/SQL code.. which turning PL/SQL in PL/SQL++ (or is that PL/SQL# instead? ;-)

PS. Don't forget that your above sub-types are date range specific. Hourly rates, salaries, stock options, billing rates and so on changes over time.

--
Billy