Unique column values between tables

Question

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical person can play any number of the roles, and the roles need unique emails. So for example, Alice is a leader (with email [email protected]), Bob is the contact ([email protected]), but Bob is also one of the players ([email protected]), along with Charlie ([email protected]). Each person can be a part of only a single team.

I already have the teams and people in two tables:

+-----+
|teams|
+-----+
|id   |
|name |
|score|
| ... |
+-----+

+-----+
| ppl |
+-----+
|id   |
|name |
| ... |
+-----+

I would like to store the roles in a database. One thing I came up with is three tables: team-leader (1:1), team-contact (1:1), team-player (1:many), with each of them having teamID, personID, and email address. The problem is that you can enter duplicate emails for people appearing in two tables.

Other idea is to have one table with all the roles. This solves the email problem, but I don't know how to restrict, for a single team, one leader/contact, with any number of players.

Does anyone have ideas of constraints or a redesign that would solve this?

Answer 1

If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:

By putting the TeamId in the People table you enforce the fact that a Person can only be on one team. You then have a many:many relationship using a cross join table between People and Roles. This allows a person to have multiple roles. By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role. You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.

The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact. You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId & RoleId WHERE the RoleIds are those that can only have one per team. I haven't tried it though.

Answer 2

I see one possible design with 4 tables, (If I understand your question), These PK will be auto incremented

Table Team

TeamId (PK)
..Other Columns

Table Player

PlayerId (PK)
TeamId (FK)
..Other Columns

Table RoleType

RoleId (PK)
RoleName

Table EmailRoleWise

PlayerId (FK)
RoleId (FK)
EmailId