I am trying to design a database in Access for an eSport league (specifically League of Legends North America). I will post what I have and then explain my issue.

Current ER Diagram

My issue comes when I try to normalize TEAMROSTER and PLAYERNAME. Each player can only belong to one team, but each team has 5 players in the 5 positions that you see I have listed in TEAMROSTER. I am not sure if that will be allowed when I try to create the relational schema. I will post the schema that I have created.

Relational Schema

I am not sure if this is correct or not, as I am trying to relate the tables together and just truly don't understand how I should do this. This is the first database that I have tried to create.

  • You have same data in two tables (teamname in league and teams) I suppose that players can change teams over the season (s). Or you will have only current state of the teams. And how come that teamroster is only 5 players. I suppose that you will track matches and records. So from that table you could get wins and loses. You will get TeamName from Teams table. – Mladen Uzelac Jun 20 '15 at 17:56
  • Did you consider studying "Database normalization". There are plenty of material on the internet. – Mladen Uzelac Jun 20 '15 at 18:41

IMHO This is not good design. You have teamname in league table and teams table.
What also is not good to have mixed conventions (plural and singular for table names)

I would note that you should also put unique index on this fields: toplane, jungler, midlane, adc, support.

create unique index teamroster_unique_idx on (toplane, jungler, midlane, adc, support);

This is valid for PostgreSQL, check your RDBMS documentation.

And also CHECK constraint that every value is different, you don't want to have all positions with same players. ;)

Unique index is to have unique combinations (no duplicates), and check constraint to have all different values.

PS. You created logical design well, but you need to translate it to physical design
(create that in your RDBMS)

| improve this answer | |
  • No I won't do this that way. A check constraint is unneeded if he would use a proper table design (in this case). :-) – Ionic Jun 20 '15 at 17:28
  • 1
    wouldn't do (grammar nazi) B-) – Mladen Uzelac Jun 20 '15 at 17:38
  • 1
    Ah sorry. ;-) but I try to give my best. I normally write German. ;-) But thanks for the correction. :-) – Ionic Jun 20 '15 at 17:40

Well the database design is ok. But I would suggest one change.

Instead of making a direct join between Teamroster and Player, I would suggest this design:

Table Player:

id (PK)

Table Teams:

id (PK)

Table: PlayerTeamPositions

team_id (PK) (FK references table teams)
player_id (PK) (FK references table players)
position_id (FK references table positions)

Table: Positions

id (PK)

You can create a combined unique primary key on the table PlayerTeamPositions which will prevent that one player can be placed on two different positions in the same team. But the same player can take another position in another team.

If this isn't intended, you can additionally add another join-table to prevent that one player can be added to two different teams.

But with this database design you'll prevent many circumstances right away in your database system instead in your application (which could fail).

| improve this answer | |
  • And his database is not ok. But he can use unique index on team_id, player id so player cannot be in two teams concurrently. I think your design is ok-ish. :) – Mladen Uzelac Jun 20 '15 at 17:54

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.