Group Membership - One-to-Many Dilemma

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.

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

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

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

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

Your thoughts would be greatly appreciated! Yael Received on Thu Dec 19 2002 - 21:50:04 CST