[Tfug] Inheritance in RDBMS's (PostgreSQL, Oracle, et al.)

[Terence Rudkin]

First an oracle example using types.

<code>
SQL> create or replace  type person as object ( name varchar2(128), phone
char(10) ) INSTANTIABLE NOT FINAL ;
  2  /
SQL> create or replace type employee under  person ( dept_no number ) ;
  2  /
SQL> CREATE TABLE person_obj_table OF person ;
SQL> insert into person_obj_table values ( employee( 'Fred', '5205551212',10
) );
SQL> insert into person_obj_table values ( person( 'Joe', '5205559999' ) );
SQL> select value(p) from person_obj_table p;

VALUE(P)(NAME, PHONE)
------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE('Fred', '5205551212', 10)
PERSON('Joe', '5205559999')

SQL> desc person_obj_table
 Name
Null?    Type
 ------------------------------------------------------------------------
-------- -------------------------------------------------
 NAME
VARCHAR2(128)
 PHONE
CHAR(10)


SQL> select  p.phone from person_obj_table p;
PHONE
----------
5205551212
5205559999

</code>

On Thu, Aug 6, 2009 at 12:19 PM, Bexley Hall <bexley401 at yahoo.com> wrote:

> Hi,
>
> What's the downside of using inheritance in designing
> data models for RDBMS's?

Extremely difficult to  maintain.  If the base type changes then the data
would need to be reloaded.  The simple type I showed does not have methods
but adding verious member functions can be very helpful..
Need to practice good discipline in coding and naming

>
> Converseley, are there any (LESS OBVIOUS) advantages
> to this approach?

If the base type is well defined addition can be easy.  Doing  versioning
can become a snap  because base columns are there and additional columns are
there based on version.

>
>
> Any general issues to avoid or exploit to take better
> advantage of this capability?

Create constructor and helper methods to make  access even smother.

TR
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://tfug.org/pipermail/tfug_tfug.org/attachments/20090806/aadae959/attachment-0002.html>