Re: Problem with inheritance

Marc SCHAEFER wrote:

> 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.

I like the idea, there's just one problem, a user can be both a student and an
employee...