Database question

[Paul Lussier]

Dan Coutu <coutu at snowy-owl.com> writes:

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

Because the table doesn't really exist yet and I edited too much from
what I cut'n'pasted :) The intent is to have the id be defined
similarly to that in the machines table.

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

The subclass idea sounds very interesting.  I'm interested to see how
this would work.  So, I'd have instead of a single classes table, I'd
have two tables, class and class_detail?  I think I'm not following
the logic here.  Could you explain a little more, perhaps with some
examples?

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

This strikes me as more confusing and troublesome...
-- 

Seeya,
Paul