Re: Problem with inheritance

On Fri, 26 Jan 2001, Alfonso Peniche wrote:

>           user
>               |
>    ----------
>    |                    |
> student      employee

Why not store the common data between student and employee in user, and
then store the additional data for student and employee in the relation
itself, implemented as a table ?

CREATE TABLE user (id SERIAL,
                   created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                   first_name VARCHAR(30) NOT NULL,
                   last_name VARCHAR(30) NOT NULL,
                   birth TIMESTAMP NOT NULL,
                   unix_uid INT2 NOT NULL,
                   email VARCHAR(30) NOT NULL,
                   UNIQUE(id), PRIMARY KEY(id));

CREATE TABLE is_student (user_id REFERENCES user NOT NULL,
                         section VARCHAR(2) NOT NULL, /* CS, PH, etc */
                         year INT4 NOT NULL DEFAULT 1);

CREATE TABLE is_employe (user_id REFERENCES user NOT NULL,
                         laboratory INT4 NOT NULL,
                         salary MONEY NOT NULL);

Probably the VARCHAR could be changed into TEXT.

Now, if you want to get all data about all student named 'Wilhelm Tell':

   SELECT u.*,is.section,is.year
   FROM user u, is_student is
   WHERE (u.first_name LIKE 'Whilhelm')
     AND (u.last_name LIKE 'Tell')
     AND (u.id = is.user_id);

When the student becomes an employee, as this happens some time, you just
need to do something like:

   BEGIN WORK;
      DELETE FROM is_student WHERE (user_id = ?);
      INSERT INTO is_employe (user, laboratory, salary)
         VALUES (?, 42, 50000);
   COMMIT WORK;

? represents here the user id, as with the Perl DBI binding.