Database question

[Paul Lussier]

This question specifically deals with PostrgreSQL and other
SQL-compliant databases.  I say this, because the question deals with
foreign keys and constraints, which I'm pretty sure MySQL doesn't deal
with properly, if at all.

I have the following table, which most other tables reference:

hosts=# \d machines
                               Table "public.machines"
 Column       |          Type          |               Modifiers         
--------------+--------------------------+---------------------------------
id            | integer                 | not null default nextval('ids'::text)
itemtag       | text                    | not null
model         | integer                 | not null
location      | integer                 | not null
monitor_temp  | boolean                 | not null
serial_number | character varying(64)   |

Indexes:
    "machines_pkey" PRIMARY KEY, btree (id)
    "machines_itemtag_key" UNIQUE, btree (itemtag)
Foreign-key constraints:
    "machines_model_fkey" FOREIGN KEY (model) REFERENCES machine_models(id)
    "machines_location_fkey" FOREIGN KEY ("location") REFERENCES locations(id)

And this table:

hosts=# \d classes
                               Table "public.classes"
 Column       |          Type          |               Modifiers         
--------------+------------------------+---------------------------------
id            | integer                | not null 
class         | text                   | not null 

Indexes:
    "classes_pkey" PRIMARY KEY, btree (id)

I want to create a table which has the following:

hosts=# \d class_members
                               Table "public.class_members"
 Column       |          Type          |               Modifiers         
--------------+------------------------+---------------------------------
id            | integer                | not null 
member        | text                   | not null 

Indexes:
    "classes_members_id_pkey" PRIMARY KEY, btree (id)

However, I want to restrict the member column by restricting the data
in it to also exist *either* in machines.id *OR* in classes.id.  The
reason for this is that a class member can either be a machine or
another class (think netgroups here).  Does anyone know how to do
this, or if it's even possible?

I suppose one solution is to just not have nested classes and
explicitly list each machine that's a member of any given class as
such, but, well, that's not overly elegant :)

Thanks for any insight.
-- 

Seeya,
Paul