Back to articles list
People like to play. So we invented games and placed them on boards. And then computers came. Now we can play against the computer or against human opponents all over the world – or right next to us.
When we play against human opponents we want to have a ranking that will show others how good we are. Maybe it will scare opponents. Or we could just rub someone’s nose in our greatness. Or use our stats to feed our ego. And it would be nice if we could review previous games and learn from our mistakes.
Do I hear cries for a database to manage these rankings? Designing a database to store results from online board games is what we’ll explore in this article.
The Board Games We’ll Talk About
We’ll discuss how we can store results and moves made during an online match. We’ll do this by way of examples from four well-known board games:
-
Tic-tac-toe
-
Chess
-
Checkers
-
Nine Men’s Morris
Hopefully, you’ve heard of these games and you know their basic rules. Each game is different, but they do have things in common:
- All of them have two players.
- We know which player starts first. After that, players move alternately.
- We can think of each board as a matrix.
Game Database Model Requirements in a Nutshell
The idea is to design a database model that would store all the data related to a single game instance. The game is played via website, while the database is used to store information about players’ actions and success rates. Our data model should cover all four previously-mentioned board games. It would also be nice if it was flexible enough to cover additional games.
A Quick Introduction to the Model
Our model has to be able to handle many board games. Each game can have a different number of players, different board dimensions (for both 2D and 3D boards), and different notations for pieces and moves. If we use it for simple games, some of the attributes will always contain NULL values. That’s the price we’ll have to pay if we want to generalize and use a strictly relational database model.
Players and Matches
The player table is where we’ll store a list of all our registered players. All attributes in the table are mandatory. The user_name and email attributes are the alternate (unique) keys. Only one player can use a username, and each player’s email address must be unique. We don’t want one person with multiple accounts; creating multiple accounts is usually related to cheating and that will put fair players off.
The nick_name attribute contains the user’s screen name; rating is based on the user’s results. For example, a player’s chess rating is calculated using the match result, with the player and opponent ratings factored in. Ratings change over time, so we cannot easily calculate our current rating based only on the results of our previous matches.
If we wanted to create a site where a user plays a game and we don’t store any data, we wouldn’t need other tables. If we allowed people to play games without registering first, even the player table wouldn’t be necessary.
If we want to make our games more interesting, some kind of scoring and analysis would be great. The remaining tables will enable us to add these functions.
First, let’s look at the game table.
The start_time attribute is mandatory and will be inserted at the beginning of the game. The end_time attribute will be filled in when the game ends, so we must leave it optional.
All of our example games are for two players. For them, the max_number_of_players will always contain the value 2. Also, we’ll need to have two records for that game in the participant table before we start.
Some board games can have different numbers of players. For example, Mensch ärgere dich nicht (a German game similar to Sorry! and Trouble) can have 2 to 4 players. In this case, we will want to increase or decrease the maximum number of players that can join a single match.
The player_started_id is a reference to the player table and contains the id of the player who initiates a match.
In all four example games – as in most board games – the board dimensions are fixed. However, in some games it’s possible for the player to choose a board size. It’s wise to reserve an attribute for that purpose. The reason we’ve chosen the varchar type for board_dimensions is to keep our model flexible enough to store all possible notations (e.g. 8x8, 5x5x5). We’re leaving the front end to interpret it.
The result_id attribute tells us how a specific match has ended. Like end_time, it’s not mandatory because some matches may not have a result. We’ll assign this when the match is over.
The move_time_limit attribute is the timeframe each player has to make one move; game_time_limit is the total time that players have to finish the game. Both of them can be used to force players to move or to finish the match. For tic-tac-toe, checkers and Nine Men’s Morris, it’s important that players move within a reasonable time. Chess has a time limit for the whole game, but not for individual moves. We’ll store both of these values as strings and parse them to get the exact time and time units. (This structure also covers a situation unique to chess, where a match lasts, say, 25 minutes and players get +10 seconds for each move they make.)
The participant table is where we’ll connect players and matches. The attributes player_id and game_id are references to the player and game tables. Together, they form the alternate (unique) key of the table.
The score attribute is used to store whatever score a player achieved in a given game. In most cases, we’ll have three possible scores: 1 for a win, ½ (0.5) for a tie and 0 for losing the game. This kind of scoring covers all four board games. For games with more than two players, we could implement a system where the winner gets 3 points, second-place finishers get 1 point and all others get 0 points. The default score value is 0, so if a player loses due to disconnection we’ll already have the correct score. That is also the reason why this attribute is mandatory.
The result table is a simple dictionary storing all possible endings. For two-player games, the set of values might be: “player1 wins”, “player2 wins”, “player1 wins on time”, “player2 wins on time”, “player1 wins due to disconnection”, “player2 wins due to disconnection”, “draw (agreed)” and “draw (material)”.
For chess, we should add a few more options: “draw (threefold repetition)”, “draw (stalemate)”, “draw (fifty-move rule)”.
Pieces and Moves
By placing game pieces, players can win or lose their match. But not all pieces function alike in every game.
In some games, all pieces have the same abilities; the color of the pieces determines which player owns them. That is the case for checkers and Nine Men’s Morris. Tic-tac-toe basically has a single piece of two different shapes (X and O) that may or may not be different colors. Chess, on the other hand, has 6 types of pieces in two colors. Piece descriptions are stored in the name attribute of the piece table.
We can use the starting_position attribute to differentiate pieces of the same type when they move on the board. This data can also simplify analytics. Games like checkers and chess have defined starting positions; tic-tac-toe and Nine Men’s Morris will leave this attribute empty.
In all four example games, the board is a 2D array (or matrix) and we can store positions as two integer values. That might not be the case for all games. For flexibility, we’ll again use the varchar type here.
The move table stores all the moves made during that game. The attributes game_id, piece_id, participant_id and move_type_id are references to the tables containing games, pieces, game participants, and move-type data.
The move_order attribute saves information about move order in the game. It starts at 1 and increases by 1 after every single move.
The move_notation attribute exists only for certain games. In chess, it’s in a human-readable format that enables us to recreate whole match. For example, Nf3 describes moving the knight to the f3 square. When players move game pieces, the resulting string is formatted so we can simply store it.
The from_position and to_position attributes store how the piece moved. In chess, both knights could move to an f3 square; these attributes specify which knight made the move. The from_position attribute is not mandatory, since in tic-tac-toe we start by putting a piece from ‘nowhere’ onto a square. The same happens at the beginning of Nine Men’s Morris, but after all nine men are placed on the board, they start to move from one position to another.
Now we know what piece is moved, where it started, and where it moved to. All the logic of the game is implemented on the front end, so these stored moves should be enough to simulate a match. The move_type dictionary is an add-on to the model that gives us more information to simplify analytics.
The idea is to have at least these two values: “placing element to a square” and “placing element to a square and taking opponent’s element”. In chess, for instance, the sequence of moves where players exchange pieces is crucial to determining the next stable state on the board. We can then analyze that state and conclude which player is in a better position.
Modeling a single master database to cover every board game is impossible. Still, it’s nice to have a structure that could cover more games. Using the same structure could simplify analytics and help us build better AI players. Please suggest other board games that could fit this model, or tell us about changes that could expand it to cover more games.
P.S. I was not joking about feeding egos in the introduction. Just go to a local chess club and you’ll see what I’m talking about. ☺
