What Do Poker, Blackjack, Belot and Préférence Have to Do with Databases?

How to design a database flexible enough to accommodate several very different card games.

Recently, we showed how a database could be used to store board game results. Board games are fun, but they’re not the only online version of classic games going. Card games are also very popular. They introduce an element of luck into gameplay, and there is much more than luck involved in a good card game!

In this article, we’ll focus on building a data model to store game matches, results, players and scores. The main challenge here is storing data related to many different card games. We could also consider analyzing this data to determine winning strategies, improve our own playing skills, or build a better AI opponent.

The Four Card Games We’ll Use in Our Database

Because players can’t control the hand they are dealt, card games combine strategy, skill and luck. That luck factor gives a beginner the chance to beat an experienced player, and it makes card games addictive. (This differs from games like chess, which rely heavily on logic and strategy. I’ve heard from many players that they are not interested in playing chess because they can’t find opponents at their skill level.)

We’ll focus on four well-known card games: poker, blackjack, belot (or belote) and préférence. Each of them has relatively complex rules and requires some time to master. The ratio of luck vs. knowledge is also different for each game.

We’ll take a quick look at the simplified rules and specifics for all four games below. Game descriptions are pretty sparse, but we’ve included enough to show the different modes of gameplay and the diverse rules we’ll encounter during the database design process.

Blackjack:

• Deck: One to eight decks of 52 cards each; no joker cards
• Players: Dealer and 1 or more opponents
• Unit Used: Usually money
• Basic Rules: Players get 2 cards that only they can see; the dealer gets two cards, one face-up and the other face-down; each player decides to draw more cards (or not); the dealer draws last. Cards have assigned point values ranging from 1 to 11.
• Possible Player Actions: Hit, Stand, Split, Surrender
• Goal & Victory Condition: The sum of a player’s cards is greater than the dealer’s; if any player goes over 21, that player loses.

Poker (Texas Hold’Em):

• Deck: Standard (also known as French suit) 52-card deck; no joker cards. Cards are most often red and black in color.
• Players: Two to nine; players take turns dealing
• Unit Used: Usually chips
• Basic Rules: Each player starts by being dealt two cards; players place their bets; three cards are dealt face-up in the middle of the table; players again place their bets; a fourth card is placed in the middle and players bet again; then the fifth and final card is placed and the last round of betting is completed.
• Possible Player Actions: Fold, Call, Raise, Small Blind, Big Blind, Reraise
• Goal: Combine the best possible hand of five cards (from the two cards in the player’s hand and the five cards in the middle of the table)
• Victory Condition: Usually to win all the chips on the table

Belot (Croatian variant of Belote):

• Deck: Usually the traditional German or Hungarian 32-card deck; no joker cards
• Players: Two to four; usually four players in pairs of two
• Unit Used: Points
• Basic Rules: For a four-player game, each player gets six cards in hand and two cards face-down; players first bid for trump suit; after trump is determined, they take the two face-down cards and place them in their hand; a declaration round follows, during which certain card combinations are announced for additional points; play continues until all cards have been used.
• Possible Player Actions: Pass, Bid Suit, Declaration, Throw Card
• Goal for the hand: To win more than half the points
• Victory Condition: Be the first team to score 1001 points or more

Préférence:

• Deck: Most often a traditional German or Hungarian 32-card deck; no joker cards
• Players: Three
• Units: Points
• Basic Rules: All players are dealt 10 cards; two “kitty” or “talon” cards are placed in the middle of the table; players determine if they want to bid on a suit; players decide to play or not.
• Possible Player Actions: Pass, Bid Suit, Play, Don’t Play, Throw Card
• Goal: Depends on the variant of Préférence being played; in the standard version, the bidder must win a total of six tricks.
• Victory Condition: When the sum of all three players’ scores is 0, the player with the lowest number of points wins.

Why Combine Databases and Card Games?

Our goal here is to design a database model that could store all the relevant data for these four card games. The database could be used by a web application as a place to store all the relevant data. We want to store initial game settings, game participants, actions taken during play, and the outcome of a single deal, hand, or trick. We must also keep in mind the fact that a match can have one or more deals associated with it.

From what we store in our database, we should be able to recreate all the actions that took place during the game. We’ll use text fields to describe victory conditions, game actions, and their results. These are specific for each game and the web application logic will interpret the text and transform them as needed.

A Quick Introduction to the Model

This model enables us to store all the relevant game data, including:

• Game properties
• List of games and matches
• Participants
• In-game actions

Since games differ in many ways, we’ll frequently use the varchar(256) data type to describe properties, moves and results.

Players, Matches and Participants

This section of the model consists of three tables and is used to store data about registered players, the matches played and the players who participated.

The player table stores data about registered players. The username and email attributes are unique values. The nick_name attribute stores players’ screen names.

The match table holds all the relevant match data. Generally, a match is composed of one or more card deals (also known as rounds, hands or tricks). All matches have set rules before play begins. The attributes are as follows:

• game_id – references the table containing the list of games (poker, blackjack, belot, and préférence, in this case).
• start_time and end_time are the actual times when a match starts and ends. Notice that the end_time can be NULL; we won’t have its value until the game ends. Also, if a match is abandoned before it is finished, the end_time value can remain NULL.
• number_of_players – is the number of participants required to start the game
• deck_id – references the deck used in the game.
• decks_used – is the number of decks used to play the game. Usually this value will be 1, but some games use multiple decks.
• unit_id – is the unit (points, chips, money, etc.) used to score the game.
• entrance_fee – is number of units needed to join the game; this can be NULL if the game doesn’t require each player to start with a set number of units.
• victory_conditions – determines which player won the match. We’ll use the varchar data type to describe each game’s victory condition (i.e. first team to reach 100 points) and leave the application to interpret it. This flexibility leaves room for lots of games to be added.
• match_result – stores the result of the match in text format. As with victory_conditions, we’ll let the application interpret the value. This attribute can be NULL because we’ll fill that value at the same time we insert the end_time value.

The participant table stores data about all the participants in a match. The match_id and player_id attributes are references to the match and player tables. Together, these values form the table’s alternate key.

Most of the games rotate which player bids or plays first. Usually in the first round, the player who plays first (the opening player) is determined by game rules. In the next round, the player to the left (or sometimes to the right) of the original opening player will go first. We’ll use the initial_player_order attribute to store the ordinal number of the first round’s opening player. The match_id and the initial_player_order attributes form another alternate key because two players can’t play at the same time.

The score attribute is updated when a player finishes a match. Sometimes this will be at the same moment for all players (e.g. in belot or préférence) and sometimes while the match is still in progress (e.g. poker or blackjack).

Actions and Action Types

When we think of actions that players can make in a card game, we realize we must store:

• What the action was
• Who performed that action
• When (in which deal) the action took place
• Which card(s) were used in that action

The action_type table is a simple dictionary that contains the names of player actions. Some possible values include draw card, play card, pass card to another player, check and raise.

In the action table, we’ll store all the events that happened during a deal. The deal_id, card_id, participant_id and action_type_id are references to the tables that contains deal, card participant and action_type values. Notice that the participant_id and card_id can be NULL values. This is due to the fact that some actions are not made by players (e.g. the dealer draws a card and places it face-up), while some don’t include cards (e.g. a raise in poker). We need to store all these actions to be able to recreate the entire match.

The action_order attribute stores the ordinal number of an in-game action. For example, an opening bid would receive a 1 value; the next bid would have a 2 value, etc. There can’t be more than one action happening at the same time. Therefore, the deal_id and action_order attributes together form the alternate key.

The action_notation attribute contains a detailed description of an action. In poker, for example, we can store a raise action and an arbitrary amount. Some actions could be more complicated, so it’s wise to store these values as text and leave the application to interpret it.

Deals and Deal Order

A match is composed of one or more card deals. We’ve already discussed the participant and the match tables, but we’ve included them in the image to show their relation to the deal and deal_order tables.

The deal table stores all the data we need about a single match instance.

The match_id attribute relates that instance to the appropriate match, while start_time and end_time denote the exact time when that instance started and when it was finished.

The move_time_limit and the deal_result attributes are both text fields used to store time limits (if applicable) and a description of the result of that deal.

In the participant table, the initial_player_order attribute stores player order for the opening match instance. Storing the orders for subsequent turns requires an entirely new table – the deal_order table.

Obviously, deal_id and participant_id are references to a match instance and a participant. Together, they form the first alternate key in the deal_order table. The player_order attribute contains values denoting the orders players participated in that match instance. Along with deal_id, it forms the second alternate key in this table. The deal_result attribute is a text field that describes the result of the match for an individual player. The score attribute stores a numeric value related to the deal result.

Suits, Ranks and Cards

This section of the model describes the cards we’ll use in all of the supported games. Each card has a suit and rank.

The suit_type table is a dictionary that contains all the suit types we’ll use. For suit_type_name, we’ll use values like “French suits”, “German suits”, “Swiss-German suits” and “Latin suits”.

The suit table holds the names of all the suits contained by specific deck types. For example, the French deck has suits called “Spades”, “Hearts”, “Diamonds” and “Clubs”.

In the rank dictionary, we’ll find well-known card values like “Ace”, “King”, “Queen” and “Jack”.

The card table contains a list of every possible card. Each card will appear in this table only once. That is the reason that the suit_id and rank_id attributes form this table’s alternate key. Both attributes’ values can be NULL because some cards don’t have a suit or a rank (e.g. joker cards). The is_joker_card is a self-explanatory Boolean value. The card_name attribute describes a card by text: “Ace of Spades”.

Cards and Decks

Cards belong to decks. Because one card can appear in multiple decks, we’ll need an n:n relation between the card and deck tables.

In the deck table, we’ll store the names of all the card decks that we want to use. An example of values stored in the deck_name attributes are: “Standard 52-card deck (French)” or “32-card deck (German)”.

The card_in_deck relation is used to assign cards to appropriate decks. The card_id – deck_id pair is the alternate key of the deck table.

Match Properties, Decks and Units Used

This section of the model contains some basic parameters for starting a new game.

The main part of this section is the game table. This table stores data about application-supported games. The game_name attribute contains values like “poker”, “blackjack”, “belot” and “préférence”.

The min_number_of_players and max_number_of_players are the minimal and maximal number of participants in a match. These attributes serve as boundaries for the game, and they are shown on-screen at the start of a match. The person who initiates the match must select a value from this range.

The min_entrance_fee and the max_entrance_fee attributes denotes the entrance fee range. Again, this is based on the game being played.

In possible_victory_condition, we’ll store all the victory conditions that could be assigned to a match. Values are separated by a delimiter.

The unit dictionary is used to store every unit used in all our games. The unit_name attribute will house values like “point”, “dollar”, “euro” and “chip”.

The game_deck and game_unit tables use the same logic. They contain lists of all the decks and units that can be used in a match. Therefore, the game_id – deck_id pair and the game_id – unit_id pair form alternate keys in their respective tables.

Scores

In our application, we’ll want to store the scores for all players who participated in our card games. For each game, a single numeric value is calculated and stored. (The calculation is based on the player’s results in all games of a single type.) This player score is similar to a rank; it lets users know roughly how good a player is.

Back to the calculation process. We’ll create an n:n relation between the player and game tables. That is the player_score table in our model. The player_id and the score_id” together form the alternate key of the table. The “score attribute is used to store the previously-mentioned numeric value.

There are variety of card games that use very different rules, cards and decks. To create a database that stores data for more than one card game, we need to make some generalizations. One way we do this is by using descriptive text fields and letting the application interpet them. We could come up with ways to cover most common situations, but that would exponentially complicate the database design.

As this article has shown, you can use one database for many games. Why would you do this? Three reasons: 1) you can reuse the same database; 2) it would simplify analytics; and this would lead to 3) the building of better AI opponents.