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