Thread

1. Problem with inheritance

   Alfonso Peniche <alfonso@iteso.mx> — 2001-01-26T16:09:00Z

   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
   
   
   

2. Re: Problem with inheritance

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-26T16:18:53Z

   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
   
   

3. Re: Problem with inheritance

   Marc SCHAEFER <schaefer@alphanet.ch> — 2001-01-26T16:32:17Z

   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.
   
   
   

4. Re: Problem with inheritance

   Alfonso Peniche <alfonso@iteso.mx> — 2001-01-26T23:49:33Z

   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.