Re: Group Membership - One-to-Many Dilemma

In article <7a808e4e.0212191950.37f19239_at_posting.google.com>, Yael <yaelr_at_locus.com.au> wrote:
>I'd like to pick your brains on the correct way to approach this
>situation:
>
>I have Staff, Teams and Roles.
>A team is made up of staff members in different roles.
>
>Some roles are predefined, and every team must have these roles filled
>(eg: every team must have a manager and a supervisor).
>Other roles are user-defined and can be added to a team as required
>(eg: clerk).
>
>So the Team to Member relationship is not simply one-to-many, it's
>one-to-two-or-more, which seems very ugly to me.

It's a ternary relation so the *-to-* terminology doesn't really apply here (it only applies to binary relations).

>The membership table would be
>Team Id*
>Role*
>Member (staff Id)

Looks good, but do you really not want Member in the primary key? Can there only be one clerk in a team? Are you sure that his is true for any role?

>But which is better:
>
>1. Defining the Team table like this
>Team Id*
>Team Name
>Manager (staff id)
>Supervisor (staff id)
>
>And the membership table for additional roles only
>
>OR
>2. Defining the Team table like this
>Team Id*
>Team Name

This is the simplest and that is always a good argument. The advantage of #1 is that you can for some queries avoid the join with the membership table. Wheter that is important depends on your case.

>and the membership table must contain at least one Manager and one
>Supervisor and may contain other roles

Don't forget to add this constraint to your database constraints.

>OR (okay, it's a 3 way dilemma - trilemma?)
>3. Defining the Team table as in 1, but repeating the manager and
>supervisor in the membership table (ie redundancy - gasp!)

Redundancy is bad, but you already knew that.

• Jan Hidders