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@example.com), Bob is the contact (firstname.lastname@example.org), but Bob is also one of the players (email@example.com), along with Charlie (firstname.lastname@example.org). 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?