Database question

[Paul Lussier]

Ray Cote <rgacote at AppropriateSolutions.com> writes:

>>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?
>
> Triggers.
>
> The design is not relational so you cannot do this with foreign keys.
> Foreign keys enforce a column-to-column relationship.
> You can do this with a trigger that looks in the two places.
>
> Whenever you run into situations like this, it is always good to
> step-back, consider the overall design to see how you ended up in this
> situation, and see if a fully-relational design can be
> implemented. However, when you really need it this way, triggers are
> the way to go.

Heh, what got me here was trying to map the concept of netgroups,
which is nested/hierarchical onto a relational construct :)

Netgroups don't *have* to nested, so I could make this a purely
relational, one-to-one design if I wanted to, simply by taking any
current netgroup definition and fully expanding any nested group
within it before dumping the data into the tables.  That would surely
solve the problem.  However, when trying to re-generate the netgroup
table from the database in the future, instead of having netgroup
definitions like:

  bar  (alpha,,) (beta,,)
  foo  bar (baz,,) (bif,,)

where netgroup foo includes netgroup bar and adds machines baz and
bif, I'd have it explicitly defined as:

  bar  (alpha,,) (beta,,)
  foo  (alpha,,) (beta,,) (baz,,) (bif,,)

In general, this is fine, but you lose the clarification that netgroup
foo is a superset of bar.  Sometimes this clarification is important,
and I'd like to retain that if possible.

Hmm, I think I'm getting what Dan was saying about splitting things
up.  I'll have think that idea through a little more carefully...

Thanks, this is all very informative.


-- 

Seeya,
Paul