abuse of inheritance?

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