Обсуждение: abuse of inheritance?

Should I be using table inheritance in this scenario?

Table "contact" has name, address, phone, email, etc. columns.
Table "staff" inherits from "contact because it has many columns in
common with "contact".

But I want to query staff and get a list of staff-- and omit non-staff
contacts. The ONLY directive only works down the inheritance tree, so I
would have to do (something like) this to get a list of staff only:

SELECT staff.whatever
FROM staff, pg_class
WHERE staff.tableoid = pg_class.oid AND pg_class.relname = 'staff'

This strikes me as kinda funny and maybe I should not have inherited
staff from contact to begin with? Not really up to speed on the whole
object-relational concept yet. :-)

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alex_rice@arc.to
alrice@swcp.com

On Wed, 2002-07-10 at 15:46, Alex Rice wrote:
> Should I be using table inheritance in this scenario?
>
> Table "contact" has name, address, phone, email, etc. columns.
> Table "staff" inherits from "contact because it has many columns in
> common with "contact".
>
> But I want to query staff and get a list of staff-- and omit non-staff
> contacts. The ONLY directive only works down the inheritance tree, so I
> would have to do (something like) this to get a list of staff only:
>
> SELECT staff.whatever
> FROM staff, pg_class
> WHERE staff.tableoid = pg_class.oid AND pg_class.relname = 'staff'
>
> This strikes me as kinda funny and maybe I should not have inherited
> staff from contact to begin with? Not really up to speed on the whole
> object-relational concept yet. :-)

I don't understand what you're wanting here - is there a misprint
above?  What you have done seems to be fine.

Assuming this structure:

     contacts
         |
        staff

If you want a list of staff:

  SELECT * FROM staff

If you want a list of non-staff:

  SELECT * FROM ONLY contacts

If you want everyone:

  SELECT * FROM contacts


If the structure is

     contacts
         |
         +-------------------+-----------------+
         |                   |                 |
        staff               salesmen          bureaucrats

getting a list of non-staff needs a union:

  SELECT col1, col2 FROM salesmen
  UNION
  SELECT col1, col2 FROM bureaucrats

or possibly an EXCEPT, if there are a lot of inherited tables:

   SELECT col1, col2 FROM contacts
   EXCEPT
   SELECT col1, col2 FROM staff

On Wednesday, July 10, 2002, at 10:27  AM, Oliver Elphick wrote:

> I don't understand what you're wanting here - is there a misprint
> above?  What you have done seems to be fine.

No misprint -- Thanks for the examples. Turns out I was just massively
confused about how SELECT works on inherited tables. Everything is
working just great now.


Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alex_rice@arc.to
alrice@swcp.com

On Wed, 10 Jul 2002, Alex Rice wrote:

> Should I be using table inheritance in this scenario?
>
> Table "contact" has name, address, phone, email, etc. columns.
> Table "staff" inherits from "contact because it has many columns in
> common with "contact".

So I'm ok with relational theory, I think, and I know OO pretty
well, at least from a programmer's viewpoint.

But I'm unsure about this table inheritance thing. What is the advantage
of using inheritance over having a staff table with the staff columns
and a FK into the contact table? What are the disadvantages? Can anyone
point to some literature on this?

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Curt Sampson writes:
>
> So I'm ok with relational theory, I think, and I know OO pretty
> well, at least from a programmer's viewpoint.
>
> But I'm unsure about this table inheritance thing. What is the advantage
> of using inheritance over having a staff table with the staff columns
> and a FK into the contact table? What are the disadvantages? Can anyone
> point to some literature on this?


"The Third Manifesto" by CJ Date and Hugh Darwen is a great text for
exploring Object Relational ideas.

(These are the two who wrote the very popular "Guide to the SQL Standard")

On Thu, 11 Jul 2002, Arguile wrote:

> Curt Sampson writes:
> >
> > So I'm ok with relational theory, I think, and I know OO pretty
> > well, at least from a programmer's viewpoint.
> >
> > But I'm unsure about this table inheritance thing. What is the advantage
> > of using inheritance over having a staff table with the staff columns
> > and a FK into the contact table? What are the disadvantages? Can anyone
> > point to some literature on this?
>
> "The Third Manifesto" by CJ Date and Hugh Darwen is a great text for
> exploring Object Relational ideas.

Yes, I've got that book, as well as the _Guide to the SQL Standard_
and many of Date's other books.

However, the appendex in T3M that deals with table inheritance can be
summarized as, "It's a stupid idea which can be implemented just as well
with a view, anyway." I tend to agree with this, but I was looking for
a contrary opinion to evaluate.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC