Trying to design database for eSports Teams - Issue with dependencies

Question

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.

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.

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.

Answer 1

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)

Answer 2

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)
PlayerName
...

Table Teams:

id (PK)
TeamName
...

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)
PositionName
...

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).