Database Schema
This is the proposed structure of how to store the data in a logical means. I am new to this type of DB, but I want to store data so that it easily exportable. The goal is to be able to get any of this data at api.namafia.com
Mafia Game
This is the first table that will store the overall game data. New entries for this table are created when a player runs the
startgame
command.
- game_id - Number - This is the threadID of thread where the
startgame
command is invoked - game_start - Number -This is the timestamp when
startgame
was invoked - game_end - Number - This is the timestamp when
result
command was invoked - game_url - String - URL of the thread where
startgame
was invoked - title - String - Title of the thread where
startgame
was invoked - status - Boolean - if the game is completed.
- signed_players - Map - This is a JSON object of signed players
- host - String - Username of the player who invoked
startgame
- not sure if we should be using user IDs.
Mafia Day
This is the table that will store all the interactions on a day by day basis. The first day of every game will auto populate when the startgame
command is invoked
- parent_id - Number - Reference to
game_id
of mafia-game table - day_id - Number - threadID of the thread where
startday
is invoked. - day_url - String - URL of the thread where
startday
is invoked - day_title - String - Title of the thread where
startday
was invoked - day_start - Number -This is the timestamp when
startday
was invoked - day_end - Number - Should we add a
endday
command or timestamp when the nextstartday
or result command is done? - votes - Map - This is JSON map of player votes.
- alive_players - Map - This is JSON map of currently alive players in the game.
Player
This table will store Player Data
- user_id - Number - User ID from discourse
- user_name - String - Current user name from discourse
- wins - Number - Number of Games Won
- loses - Number - Number of Games Lost
Nice to have player stat info if possible
- town_games - Number -Number of Town Games
- town_wins - Number - Number of Town Wins
- town_loses - Number - Number of Town Loses
- mafia_games - Number - Number of Mafia Games
- mafia_wins - Number - Number of Mafia Wins
- mafia_loses - Number - Number of Mafia Loses
- third_games - Number - Number of Third Wins
- third_wins - Number - Number of Third Wins
- third_loses - Number - Number of Third Loses