Thread: Problem with inheritance
Hi all....
I have the following inheritance relation:
user
|
----------
| |
student employee
If I insert John into table student, how can I insert him afterwards so
that he is also an employee (this could happen several days later)?
Thanx
Alfonso Peniche
Alfonso Peniche <alfonso@iteso.mx> writes:
> I have the following inheritance relation:
> user
> |
> ----------
> | |
> student employee
> If I insert John into table student, how can I insert him afterwards so
> that he is also an employee (this could happen several days later)?
If a student could also be an employee, then your table layout is
fundamentally wrong.
regards, tom lane
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.
Alfonso Peniche wrote: >Hi all.... > >I have the following inheritance relation: > > user > | > ---------- > | | >student employee > >If I insert John into table student, how can I insert him afterwards so >that he is also an employee (this could happen several days later)? You probably need to rethink your hierarchy. If you want to persist with inheritance, you could define another table, student_employee, which multiply-inherits student and employee. Incidentally, `user' is a reserved word; you will have to change the table name for 7.0.3, or else double-quote it every time you use it. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Wash me thoroughly from mine iniquity, and cleanse me from my sin. For I acknowledge my transgressions; and my sin is ever before me. Against thee, thee only, have I sinned, and done this evil in thy sight..." Psalms 51:2-4
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...
Tom Lane wrote: > Alfonso Peniche <alfonso@iteso.mx> writes: > > I have the following inheritance relation: > > > user > > | > > ---------- > > | | > > student employee > > > If I insert John into table student, how can I insert him afterwards so > > that he is also an employee (this could happen several days later)? > > If a student could also be an employee, then your table layout is > fundamentally wrong. > > regards, tom lane Sorry, in Informix (which I've been working on for sometime now) this is the way I would handle the inheritance. What would be the right way of doing this same thing with pgsql (considering that someone can be both a student and an employee)? Thanx for your help.
On Fri, 26 Jan 2001, Alfonso Peniche wrote:
> I like the idea, there's just one problem, a user can be both a student and an
> employee...
- If the guy is an user only, then just fill the user template
- If the guy is a student, add a tuple to the is_student relation.
- If the guy is an employee, add a tuple to the is_employee relation.
You do not need to delete the is_student if you insert into is_employee
(and backwards).
The only problem that I see with my approach is that you can create an
user which isn't neither a student nor an employee: if this is an issue
you might want to
periodically run a query like:
SELECT u.*
FROM user u
WHERE (u.id NOT IN (SELECT user_id FROM is_student))
AND (u.id NOT IN (SELECT user_id FROM is_employee))
to spot illegal entries.
(haven't tried it, though).
Or someone from the PostgreSQL or SQL experts could tell us if there is a
way to do cross-table integrity checking ?
Hi, I wanted to try in the archives how to store a md5sum best in a database (origin is 16 bytes binary, if I don't get a good answer I will use ASCII like the output of the md5sum UNIX command since this is easy to debug). I got this error: Not Found The requested URL /mhonarc/pgsql-general/search.mpl was not found on this server. Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80
On Sat, 27 Jan 2001, Marc SCHAEFER wrote: > Hi, > > I wanted to try in the archives how to store a md5sum best in a database > (origin is 16 bytes binary, if I don't get a good answer I will use ASCII > like the output of the md5sum UNIX command since this is easy to debug). > > I got this error: > > Not Found The requested URL /mhonarc/pgsql-general/search.mpl was not > found on this server. > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > They must not be in sync since I fixed that problem a few days ago. Go to http://www.postgresql.org/mhonarc/pgsql-general/search.mpl and it should work fine. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Sat, Jan 27, 2001 at 03:20:48PM +0100, Marc SCHAEFER wrote: > I wanted to try in the archives how to store a md5sum best in a database > (origin is 16 bytes binary, if I don't get a good answer I will use ASCII > like the output of the md5sum UNIX command since this is easy to debug). I'd say store it in base64 -- it should be 22 characters long, rather than the 32 characters it would be if you used hexidecimal. You should also use char(22), rather than varchar -- not that it matters much. I don't know of a way to store the actual binary format, but that might also be possible. HTH, Neil -- Neil Conway <neilconway@home.com> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed Skepticism is the agent of truth. -- Joseph Conrad