Hi,

I have a supertype and three subtypes, as attached below, how is the
best approach to implement it in Oracle (9i/10g) ?

Thank you for your help,
Krist

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

xtanto@hotmail.com wrote:
> Hi,
>
> I have a supertype and three subtypes, as attached below, how is the
> best approach to implement it in Oracle (9i/10g) ?
>
> Thank you for your help,
> Krist
>
> 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

For something this simple perhaps this:

CREATE TABLE Employee
( Employee_Number
, Employee_Name
, Address
, Employee_Type
, Date_Hired
, Hourly_Rate
, Annual_Salary
, Stock_Options
, Contract_Number
, Billing_Rate
);

With some check constraints like this one:

CHECK ((Employee_Type = 'Hourly' and Hourly_rate is not null) or
(Employee_Type <> 'Hourly' and Hourly_rate is null))

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

I think , you can try:


CREATE TYPE "my_super_type" .....etc

it's available since oracle8i;

<xtanto@hotmail.com> a écrit dans le message de news:
1118746309.163235.138790@g14g2000cwa.googlegroups. com...
> Hi,
>
> I have a supertype and three subtypes, as attached below, how is the
> best approach to implement it in Oracle (9i/10g) ?
>
> Thank you for your help,
> Krist
>
> 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
>