Nodejs MySQL Random Table Insert

Question

I'm trying to write a discord game bot like Town of Salem, Wolvesville, Feign etc.

I have a one table for users who used "join" command. table name joinedplayer

gamerid discordid 
1       user1
2       user2
3       user3
4       user4
5       user5

And i have one table for gameroles table name gameroles

roleid rolename
1      mafia1
2      mafia2
3      town1
4      town2
5      town3

I'm trying to when start command used create a playernew table like: Table name playernew

playerid discordplayerid roleid
1        user1           5
2        user2           4
3        user3           1
4        user4           3
5        user5           2

When game stopped delete "playernew" and table and when game started create a new "playernew" table with different index

How can i do that?

Answer 1

In Sql, there is a concept called Foreign Key. So, you can relate tables with each other.

In your case, first create a game table, id must be your primary key

id      name
1   user1's game

then, add gameId to your User table

playerid discordplayerid roleid gameId
1        user1           5         1
2        user2           4         1
3        user3           1         1
4        user4           3         1
5        user5           2         1

To add foreign key to your user table

ALTER TABLE User
ADD FOREIGN KEY (gameId) REFERENCES game(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

So, If you just delete a game from game table, it will automatically delete all related users through foreign key.