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