1

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 alice.leader@game.com), Bob is the contact (bob.contact@game.com), but Bob is also one of the players (bob.player@game.com), along with Charlie (charlie.player@game.com). 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?

  • If Alice is a leader in 2 teams, will she have the same (leader) email in both? – ypercubeᵀᴹ Oct 2 '14 at 15:01
  • One person can be in only one team, edited. – Luther Frane Oct 2 '14 at 15:03
  • If a person can only have one leader and one contact, why not columns LeaderID and ContactID? (Nullable, of course.) – Aaron Bertrand Oct 2 '14 at 15:04
  • @AaronBertrand: Where would I store their emails then? Emails are not tied to a person, but to a personXrole combination. – Luther Frane Oct 2 '14 at 15:10
  • 1
    @LutherFrane What you say in one comment contradicts the other. If a person can be in only one team, how can they have multiple emails? Or you mean different emails because someone is leader, contact and player in the same team? – ypercubeᵀᴹ Oct 2 '14 at 15:13
3

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:

enter image description here

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.

  • Nice design. As far as the "only one leader" requirement, take a look at this question: dba.stackexchange.com/questions/40324/… – spaghettidba Oct 2 '14 at 15:54
  • Very cool. I thought it would work & I knew I could create the index. Just wasn't sure about enforcement. I was going to test tonight. – Kenneth Fisher Oct 2 '14 at 15:58
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.