Database question

[Dan Coutu]

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.
>  
>