From: Logan Capaldo <logancapaldo@...>
Date: 2007-09-09T07:13:02+09:00
Subject: Re: enforce mutual exclusivity

On 9/7/07, Toby O'Rourke <ruby@tobyandbecswedding.info> wrote:
> Hi,
>
> First post, sorry if this is the wrong place or has been answered before.
>
> I'm using ActiveRecord and I want to enforce mutual exclusivity on a
> has_and_belongs_to_many.
>
> A concrete example: I have a User which can have one or more Roles
> (student, tutor, headmaster, administrator etc). However if a user is a
> student they cannot hold any other role.

Despite the knee jerk reaction to direct you to the rails mailing
list, the real place to ask this question would be some group / list
dedicated to data modelling in RDBMSs. That said I think it's an
interesting question so I'm going to try and answer it :)

If student is the only that is mutual exclusive with any other role,
then one way to look at it is that student is not actually a role. One
way to do this is to pull out all the common information about a
person (name, address, phone number, what have you) and stick that
into a person_info relation. You then an have two relations, students,
and nonstudents. Both of these would simply have a field refererring
to the primary key of the person_info relation. Then your relation for
the roles would pair roles with nonstudents.

This has problems of course. If student isn't the only exclusive role,
or if the rules are more complicated (like a headmaster can't be a
tutor but they are an administrator) this kind of arrangement will
fall apart pretty quickly.

Another option is to divide the roles into N groups of mutually
exclusive roles, and have N fields in your relation. The problem with
this is that you might not be able to divide your roles like this. It
is also, like my first suggestion, brittle with respect to changes in
the rules of what roles are mutually exclusive.

Yet another option is to enumerate all legal role combos, and
associate each combo with an id. Then to assign a role or a set of
roles you just use the id.