Re: How to implement SubType/SuperType in Oracle ?

From: Tony Andrews <andrewst_at_onetel.com>
Date: 14 Jun 2005 04:24:49 -0700
Message-ID: <1118748289.243350.258390@g44g2000cwa.googlegroups.com>

xtanto_at_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)) Received on Tue Jun 14 2005 - 06:24:49 CDT