What’s Texas 42 then?
Texas 42 or just 42 is not just a Douglas Adams reference; it is also a point-trick domino game, with four players in fixed partnerships. The tiles are used like cards, partnerships score one point for each of the seven tricks, plus five points for the “nickels” ([0:5, [1:4], [2:3]) and “dimes” ([5:5], [4:6]) that are captured in those tricks. Each hand has 42 total points, hence the name of the game. The first partnership to win seven hands (“marks”) wins the game.
This game was invented in 1887 in Trappe Springs (now Garner), Texas by 12 year old William Thomas and 14 year old Walter Earl. These were two fundamentalist Baptists who were caught playing Auction Whist with playing cards and were punished for it by their parents. Fundamentalist Baptists regarded playing cards as “The Devil’s Picture Book” and did not allow card games, but had no such restrictions on domino games.
The game is played only in Texas and parts of Oklahoma. It is very popular on college campuses here. The best book ever written (and one of the very few) is WINNING 42: STRATEGY AND LORE OF THE NATIONAL GAME OF TEXAS) and you can find several Internet websites. Since there are many local variations in the rules, I would recommend this book as the definitive source for those who wish to become seriously involved in the game.
I do SQL, the language, and databases. SQL Server is just one of many products with which I work. Last year, I was at the March meeting of the Austin Postgres user group. One of the other attendees was a foreign student who had just learned to play Texas 42 (a domino game; see the sidebar for details). Being a geek, she and her brother immediately wanted to write a program for the game. Being a database guy, I immediately wanted to write a database for it. There is an old Dilbert cartoon about this in which the pointy-hair boss announces that the team is starting a new project; Wally replies that they will build a database; “But you don’t know what the project is yet”; “We don’t care, we like to build databases!”
While we talked, I realized that this was a good teaching opportunity. Most of the time, we pull out a commercial example and demonstrate a standard idiom or two. How many times have you seen an order and order details schema with a PK-FK reference? How about an adjacency list versus nested sets model for some hierarchy? Are you old enough to remember Chris Date’s SPJ (Suppliers, Parts & Jobs) database examples?
It is good have proven programming idioms for common situations, but you do not get much help when you run into a new problem. All too often, you make one of two mistakes
- Force the new problem into a known programming idiom even if the fit is not good. Unfortunately the idiom is often a sequential file with a fake record number mimicked with an IDENTITY or a GUID.
- Mimic the physical parts of the problem directly in SQL. In particular, copy the paper forms directly into table declarations, right down to physical line numbers.
What we want to do is apply some general principles to get a schema design. This is not a guarantee of a good model, but it is a place to get started.
Step One: Make a list of the entities and questions about them. Do not worry about relationships or attributes.
The reason that this is the first step is that entities can often be held, felt and seen. Begin with the physical stuff, no brains required. In this case the entities we can see sitting at the domino table are the four players and 28 dominoes. There are also a score pad, chairs and a table, beers and other junk, but we do not care about them. Well, maybe we care about the beer, but we don’t need to create an entity to prize the cap off..
Step Two: Inspect the entities, one at a time. What are their attributes?
The Dominoes:
The 28 domino tiles are pretty solid entities. Each one is unique, and they have a natural key made up of the two ends. The [a:b] and the [b:a] tiles are the same entity; all you did was spin the stone about its nail. Do I need to worry about this? Should a table have 28 unordered or 49 ordered pairs in it?
But the nickels and dimes are special cases for scoring. Should we have a table for the tiles? Should the table have the point values of each tile? That mean a tile would be 0.25, 5.25 or 10.25 points so that we can add the four tiles of a trick we get the score for the trick.
The Players
Let’s follow the usual convention for card games of naming the players by the four compass points. North, South, East and West. The four players are actually two partnerships (North & South, East & West). The partnership has a score, not the players. I wonder if partnerships should be part of the table or the application?
Step Three: Look for abstract entities. Not all things in a dart model are physical.
The concept of a ‘hand’ and a ‘game’ are surely abstractions that become part of the data model. The hands are played much like other trick taking games with bidding. Each hand has a winning partnership and seven wining hands becomes a winning game. The cycle starts with a first bidder then rotates clockwise; this sequence gives us a natural key for the hands.
Each hand has one round of bidding in which each player can pass or bid a contract from 30 to 42 points (one mark). The one mark bid is saying that you will take all the tricks, but a following player can bid two marks, then three marks and even four marks if they also think they can take every trick.
Once the bid is won, the winner announces the trump suit for this hand after the bid and leads the first trick. Trump can be blank, one to six, doubles as their own suit or no trump. Let’s not worry about the particulars, just that once we have a trump suit, we have to have some way to use it to decide legal plays and who wins each trick.
Another difference from other trick taking games is that each partnership keeps it’s tricks face up on the table. If the bidder makes or exceeds his contract that partnership wins a mark immediately.
I have to have the trump and the winner in my model of the hand, but do I want the losing bids? As a player, what my partner bids is important to me for my strategy as in any partnership game. But this is the database side of the problem. So I only care about the winner, the bid an the trump?
Step Four: Try a first hack at the SQL with columns declarations.
Just come up with something you can play with. Start to worry about how you can encode the values of the attributes. Most of the time, we either have an industry-standard encoding that we can use or there is a natural scale such as a sequence or count.
As far as I know, there is no ISO standard for representation of the tiles, so I will use a string that mimics a tile as a pair of digits separated by a colon.
The bids are numeric values. I can use zero for a “Pass”, 42 for one mark, 84 for two marks, 168 for three marks, and 336 for four marks.
The player’s names are already set as the compass points, as per the usual convention for card games.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE Dealt_Hands (hand_seq INTEGER NOT NULL CHECK(hand_seq > 0), player_name CHAR(1) NOT NULL CHECK (player_name IN ('N', 'S', 'E', 'W')), PRIMARY KEY (hand_seq, player_name), bid INTEGER DEFAULT 0 NOT NULL CHECK(bid IN (0, 30, 31, 32, 33, 34, 35, 36, 36, 37, 39, 40. 42, 84, 168, 336)), tile_1 CHAR(3) NOT NULL CHECK (tile_1 LIKE '[0-6]:[0-6]'), tile_2 CHAR(3) NOT NULL CHECK (tile_2 LIKE '[0-6]:[0-6]'), tile_3 CHAR(3) NOT NULL CHECK (tile_3 LIKE '[0-6]:[0-6]'), tile_4 CHAR(3) NOT NULL CHECK (tile_4 LIKE '[0-6]:[0-6]'), tile_5 CHAR(3) NOT NULL CHECK (tile_5 LIKE '[0-6]:[0-6]'), tile_6 CHAR(3) NOT NULL CHECK (tile_6 LIKE '[0-6]:[0-6]'), tile_7 CHAR(3) NOT NULL CHECK (tile_7 LIKE '[0-6]:[0-6]')); |
Well, that looks like it models a hand pretty well. I even have a few constraints on some of the columns. But there are few holes in the design. I am not recording the play of the game, just a static snapshot of the hands as dealt. While I can get the winning bid I do not have the trump or haw the hand was played. Let’s try to record a the actual play:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE Played_Hands (hand_seq INTEGER NOT NULL CHECK(hand_seq > 0), trick_seq INTEGER NOT NULL CHECK(hand_seq BETWEEN 1 AND 7), north_tile CHAR(3) NOT NULL CHECK (north_tile LIKE '[0-6]:[0-6]'), east_tile CHAR(3) NOT NULL CHECK (east_tile LIKE '[0-6]:[0-6]'), south_tile CHAR(3) NOT NULL CHECK (south_tile LIKE '[0-6]:[0-6]'), west_tile CHAR(3) NOT NULL CHECK (west_tile LIKE '[0-6]:[0-6]') ); |
and we need other information somewhere, so let’s do a table for the bid data.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Bids (hand_seq INTEGER NOT NULL CHECK(hand_seq > 0), winning_player_name CHAR(1) NOT NULL CHECK (player_name IN ('N', 'S', 'E', 'W')), PRIMARY KEY (hand_seq, player_name), bid INTEGER DEFAULT 0 NOT NULL CHECK(bid IN (0, 30, 31, 32, 33, 34, 35, 36, 36, 37, 39, 40. 42, 84, 168, 336)) ); |
The seven tiles in the Dealt_Hands and the Played_Hands have no constraints to keep the same tile from being modeled two ways, Oh, wait! There is no constraint to assure that the 28 tiles are universally unique. We can be playing Forty-Two with the domino equivalent of a Pinocle deck.
Somewhere in the application there would be a shuffle and deal, procedures to enforce the rules and score keeping. But that is not in the database side of the problem.
What I have so far are fake arrays for the tiles, right down to the fake subscript in the column names. Mmm, weren’t arrays supposed to be a bad thing? Repeated groups and non-First Normal Form and all that jazz.
But I am also doing something else that feels bad. I am mimicking the physical movement of the tiles from the player’s hands to the tricks.
Step Five: consider the possibility that you made a false start.
I made a domino an attribute of a player and of a trick. Being part of a trick feels okay, but I know that a domino is no more a part of player than a book is an attribute of an author (they are two entities with an authorship relation).
What if we “hold the tiles still” and track the location or status of each tile in the set? That would give me a 28 row table with the name of the tile as its key. That saves me the overhead of trying to assure that I have accounted for all tiles in the previous model. Oh, writing that code in SQL is a good exercise that will convince you that you want declarative constraints.
As it works out, we have another SQL idiom for status changes. Go back and review this article on state transition constraints State-Transition Constraints. Let’s do a quick skeleton with this idiom.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Dominoes (tile_name CHAR(3) NOT NULL PRIMARY KEY CHECK (tile_name LIKE '[0-6]:[0-6]'), prior_tile_status CHAR(3) NOT NULL, current_tile_status CHAR(3) NOT NULL . FOREIGN KEY (prior_tile_status, current_tile_status) REFERENCES Rulebook (prior_tile_status, current_tile_status), ..); |
My initial states are being in the hand of one of the four players. My terminal states are being in the collected tricks of one of the two partnerships. Hey, this is overkill! I am being fooled by a programming idiom that is too flexible for my situation. I do not have a lot of states and the rules of Forty_two have not changed in over a century. And I left out data about the hands and the tricks in that skeleton.
I also bet that life would be easier if we has a constant table with the do-able six set of dominoes in it. Try again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Forty-Two (hand_seq INTEGER NOT NULL CHECK(hand_seq > 0), trick_seq INTEGER DEFAULT 0 NOT NULL CHECK(hand_seq BETWEEN 0 AND 7), tile_name CHAR (3) NOT NULL REFERENCES Double-Six (tile_name) PRIMARY KEY (hand_seq, trick_seq, tile_name) tile_hand CHAR(1) NOT NULL CHECK (tile_hand IN ('B', 'N', 'S', 'E', 'W')), tile_trick CHAR(2) CHECK (tile_hand IN ('NS', 'EW')), ..); |
Here is a trick that many programmers do not know. It constructs a constant table. Casting the first row to specific data types is a safety and documentation trick.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE VIEW Double_Six (tile_name) AS SELECT * FROM (VALUES (CAST ('0:0' AS CHAR(3))), ('0:1'), ('0:2'), ('0:3'), ('0:4'), ('0:5'), ('0:6'), ('1:1'), ('1:2'), ('1:3'), ('1:4'), ('1:5'), ('1:6'), ('2:2'), ('2:3'), ('2:4'), ('2:5'), ('2:6'), ('3:3'), ('3:4'), ('3:5'), ('3:6'), ('4:4'), ('4:5'), ('4:6'), ('5:5'), ('5:6'), ('6:6')) AS X; |
I have numbered the tricks from zero to seven so I can use zero as the initial deal when players are still bidding. The NULL in the trick column means the tile has not been played yet. The partnership codes explain themselves. Besides the four compass points, I added ‘B’ for “bone yard”, the initial face-down tiles from which the players draw their hands. This lets us model the start of the game with this statement:
|
1 2 3 4 |
INSERT INTO Forty-Two (hand_seq, trick_seq, tile_name, tile_hand, tile_trick) SELECT 1, 0, D.tile_name, 'B', NULL FROM Double_Six AS D; |
Now I can write a simple procedure to deal the tiles. The procedure will need some code after the deal to be sure that the deal was good. That means all the tiles are dealt;
|
1 2 3 4 |
(NOT EXISTS (SELECT * FROM Forty-Two WHERE tile_hand = 'B')) |
Then I need to check to see that every dealt hand has seven tiles;
|
1 2 3 4 5 6 7 |
(7 = All (SELECT COUNT(*) FROM Forty-Two WHERE tile_hand IN ('N', 'S', 'E', 'W') AND hand_seq > 0 GROUP BY hand_seq, trick_seq) ) |
Oh, wait! We are missing the bid and trump data for each hand, We can use digits for the zero to six suits, then ‘D’ for doubles (special bid that treats doubles as their own suit) and ‘F’ for “follow me”, another special bid of “no trump”; the higher end of the tile lead is the suit to follow in that trick.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE Bids (hand_seq INTEGER NOT NULL PRIMARY KEY, CHECK(hand_seq BETWEEN 1 AND 7), bidder_player_name CHAR(1) NOT NULL CHECK (player_name IN ('N', 'S', 'E', 'W')), bid INTEGER DEFAULT 0 NOT NULL CHECK(bid IN (0, 30, 31, 32, 33, 34, 35, 36, 36, 37, 39, 40. 42, 84, 168, 336)), trump CHAR(1) NOT NULL CHECK (trump IN ('0', '1', '2', '3', '4', '5', '6', 'D', 'F')) ); |
Summary
I am going to stop with this analysis. If you want to keep playing with the problem, then learn the rules of the game and write code to use the Bids table to pick the winner of a trick, to update the Forty_Two table, a VIEW to compute the current score and other things.
The point of all this discussion is to expose thought processes and the Relational mindset that you can bring to a totally new problem. In ten words or less, look beyond the physical and go to the abstract
Load comments