I'm creating a database for an esports league and am having some issues putting it all together for an ERD. Here's a quick rundown on how the game works:
There are 2 teams of 5 Players each. These Players have unique positions on the team (5 total positions). The Players use unique characters for the match that can change from one match to the next but cannot change during the match. The players earn kills, assists, gold, etc (different statistics I want to record in the database) throughout the match.
I have the following tables so far:
PLAYER
Player_Name (PK)
Player_Password
Player_Picture
TEAM
Team_Name (PK)
Team_Logo
Player_ID (FK)
MATCH
Match_ID
Team_ID (FK) (Team_1 & Team_2) (Associative entity needed?)
Game_Time
Game_Date
Game_Length
I'm also stuck on where to put the STATS table and where to put the CHARACTER table and which tables these two need to connect to.
Additional notes and considerations:
-Each player uses a character for each match. This character can change from match to match. I was planning on adding a CHARACTER table but I'm unsure where to connect it.
-I would also like to keep track of different statistics for each match, for both the team and the individual players (kills, assists, gold, etc). I'm not sure where to add this table and which other tables it should be connected to. The team stats are essentially a rollup of the individual player stats (Team total kills = summation of its players' kills). I want to be able to analyze a Player's stats overall and the Player's stats for each individual character that they use.
-Each Player also has a unique position (there are 5 players/positions per team). I would also like to be able to analyze each player's stats by position.
Any help would be greatly appreciated.
Best Answer
In my opinion: Create your CHARACTER, MATCHSPLAYERSSTATS and POSITION(Top, Mid, Jungle, etc I suppose?).
Then, assuming the MATCHSPLAYERSSTATS table will have a line for each player of the match, you could for PK the keys CharacterPK(s), Match_ID and Player_Name(although I recommend not using the name as a PK), and a FK pointing to the POSITION table
This way, each MATCH would have 10 stats, each one for one player, and linking to the character used in that Match