Paul Lussier wrote:
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.
MySQL 4 and newer does handle foreign keys. MySQL 3 and older didn't.
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)
Out of curiosity why don't you define the id column here the same way
you did in the machines table with a default value. I *always* define
primary keys like that, it saves so many headaches down the road.
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?
Let me ask this, why can't a machine be a special type of class? You
could add a colum to the class table that indicates the class type and a
class type of machine could then indicate a cross-reference into the
machine table.
If you do this then the class_members foreign key constraint becomes easy.
You could then take this a bit further to allow a class to contain
sub-classes, which might be useful too because you could then create
groups of classes that could be manipulated all at once. If you go this
route then it might become a good idea to break down the class table
into two pieces, class and class_detail where class_detail might contain
(but doesn't have to contain) a class id value, machine id value, or
some other id value. If you structure the class_detail table such that
individual column types are foreign keys to specific tables then you
have full constraints checking enabled.
Another possible approach might be to chuck the class_member table
entirely and instead have multiple tables such as machine_class,
class_class, etc. that map one table to the other (machine to class,
class to class, and so forth.)
Anyway, food for thought. Hope it helps.
Dan
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.
_______________________________________________
gnhlug-discuss mailing list
gnhlug-discuss@mail.gnhlug.org
http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss
