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(at)iteso(dot)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.

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(at)iteso(dot)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(at)michvhf(dot)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(at)home(dot)com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Skepticism is the agent of truth.
-- Joseph Conrad