Re: Group Membership - One-to-Many Dilemma

From: Jan Hidders <>
Date: 20 Dec 2002 12:02:40 +0100
Message-ID: <3e02f8d0$>

In article <>, Yael <> wrote:
>I'd like to pick your brains on the correct way to approach this
>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*
>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
>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
Received on Fri Dec 20 2002 - 12:02:40 CET

Original text of this message