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:
Team_ID (FK) (Team_1 & Team_2) (Associative entity needed?)
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.