Database Design

A Database Model for Simple Board Games

Posted: March 10, 2016

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:

1. Tic-tac-toe

2. Chess

3. Checkers

4. 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. ☺

Tags

New SQL Course! Learn the basics of T-SQL for SQL Server, retrieve data from an SQL Server database and build simple reports. View course Discover our other courses: