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 [email protected]), Bob is the contact ([email protected]), but Bob is also one of the players ([email protected]), along with Charlie (charlie.pl[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?

7
  • If Alice is a leader in 2 teams, will she have the same (leader) email in both? Oct 2, 2014 at 15:01
  • One person can be in only one team, edited. Oct 2, 2014 at 15:03
  • If a person can only have one leader and one contact, why not columns LeaderID and ContactID? (Nullable, of course.) Oct 2, 2014 at 15:04
  • @AaronBertrand: Where would I store their emails then? Emails are not tied to a person, but to a personXrole combination. Oct 2, 2014 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? Oct 2, 2014 at 15:13

2 Answers 2

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.

2
  • Nice design. As far as the "only one leader" requirement, take a look at this question: dba.stackexchange.com/questions/40324/… Oct 2, 2014 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. Oct 2, 2014 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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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