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.
