SQL Tic-Tac-Toe attempt

In an attempt to practice conditional statements in SQL I decided to design a tic-tac-toe game with MySQL. Let me know if you think any of the steps could be done better.

Step 1: Create the game board:

USE test;
DROP TABLE IF EXISTS TicTacToe;
CREATE TABLE TicTacToe
    (
    ID INT NOT NULL,
    A VARCHAR(1) NULL,
    B VARCHAR(1) NULL,
    C VARCHAR(1) NULL
    );
INSERT INTO TicTacToe(ID,A,B,C)
VALUES
    (1,NULL,NULL,NULL),
    (2,NULL,NULL,NULL),
    (3,NULL,NULL,NULL);
SELECT * FROM TicTacToe;

Result:

ID   A       B       C
1    NULL    NULL    NULL
2    NULL    NULL    NULL
3    NULL    NULL    NULL

Step 2: I was going to use a global variable to determine player turn, however unfortunately I found out MySQL does not support that. So instead, I will use a table.

USE test;
DROP TABLE IF EXISTS ttt_PlayerTurn;
CREATE TABLE ttt_PlayerTurn (turn VARCHAR(1) NOT NULL);
INSERT INTO ttt_PlayerTurn (turn) VALUES ('X');
-- 1 row(s) affected

Step 3: Create a stored procedure to check if game is over or ongoing. Note: If anyone can think of a more elegant/efficient way, let me know.

```
USE test;
DELIMITER |
DROP PROCEDURE IF EXISTS ttt_CheckVictory|
CREATE PROCEDURE ttt_CheckVictory ()
BEGIN
SET
@A1 = (SELECT A FROM TicTacToe WHERE ID = 1),
@A2 = (SELECT A FROM TicTacToe WHERE ID = 2),
@A3 = (SELECT A FROM TicTacToe WHERE ID = 3),
@B1 = (SELECT B FROM TicTacToe WHERE ID = 1),
@B2 = (SELECT B FROM TicTacToe WHERE ID = 2),
@B3 = (SELECT B FROM TicTacToe WHERE ID = 3),
@C1 = (SELECT C FROM TicTacToe WHERE ID = 1),
@C2 = (SELECT C FROM TicTacToe WHERE ID = 2),
@C3 = (SELECT C FROM TicTacToe WHERE ID = 3);

CASE
-- Horizontal wins
-- Horizontal win on row 1
WHEN
@A1 = @B1 AND @B1 = @C1
THEN (SELECT *, CONCAT('Player ', @A1, ' is victorious!') AS 'Result' FROM TicTacToe);
-- Horizontal win on row 2
WHEN
@A2 = @B2 AND @B2 = @C2

There is one big thing that I don't agree with, and that's your SQL schema. In my opinion, it is not normalized.

Each row should only contain one kind of information. One row in your TicTacToe table however, contains three pieces information: The tile A, the tile B and the tile C.

You have a major scalability issue here, that your table can only contain the information for one game. You're also not able to change the size of the board without changing the entire schema, or support TicTacToe Ultimate (even though supporting TTT Ultimate would require some more work, but I believe it would be possible if you structure things correctly).

I would recommend a more normalized approach to this. So let's see what tables we could use:

• Board (with tiles), obviously, where each row contains a tile in the board

• Game, storing information about the current player and having a Board field to connect the game to a board id. If you want flexible size of the boards, you could add sizeX and sizeY fields here.

• WinConditions, to store the data about what rows/columns/diagonals are considered as winners. This will remove the win conditions from your code and add them as data. Below, I have only one board and have linked the WinConditions to a specific board. Ideally, you could create a many-to-many relationship between WinConditions and Game, so that all ordinary Tic-Tac-Toe games can share the same set of WinConditions.

Once you have this setup properly, you'll be able to support much more than just TicTacToe. The game Connect Four actually shares a lot of logic with TicTacToe, the only difference is the size and the existence of "gravity".

Here's some SQL to show an example:

CREATE TABLE Board
    (
      ID INT,
    X INT NOT NULL,
    Y INT NOT NULL,
    VALUE VARCHAR(1) NULL,
    CONSTRAINT position PRIMARY KEY (ID, X, Y)
    );

CREATE TABLE Game
    (
    TURN INT,
      BOARD INT
    );

CREATE TABLE WinConditions (
    Board INT,
  ConditionID INT,
  TileX INT,
  TileY INT
);

Setup some win-conditions:

INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 1, 0, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 1, 1, 1);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 1, 2, 2);

INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 2, 0, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 2, 1, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 2, 2, 0);

INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 3, 2, 0);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 3, 2, 1);
INSERT INTO WinConditions (Board, ConditionID, TileX, TileY) VALUES (1, 3, 2, 2);

Insert some data into the board, causing it to look like

XOO
OX_
X_X

INSERT INTO Board (id, x, y, value) VALUES (1, 1, 1, 'X');
INSERT INTO Board (id, x, y, value) VALUES (1, 1, 0, 'O');
INSERT INTO Board (id, x, y, value) VALUES (1, 0, 2, 'X');
INSERT INTO Board (id, x, y, value) VALUES (1, 2, 0, 'O');
INSERT INTO Board (id, x, y, value) VALUES (1, 0, 0, 'X');
INSERT INTO Board (id, x, y, value) VALUES (1, 0, 1, 'O');
INSERT INTO Board (id, x, y, value) VALUES (1, 2, 2, 'X');

Query to check for wins:

SELECT MAX(value), MIN(value), COUNT(value) FROM WinConditions `wc`
JOIN Board `b` ON (wc.tilex = b.x) AND (wc.tiley = b.y) AND (wc.board = b.id)
WHERE wc.board = 1
GROUP BY conditionID;

Output from query:

MAX(VALUE)  MIN(VALUE)  COUNT(VALUE)
X           X           3
X           O           3
X           O           2

By looking at this query, we can see that one condition has led to a win for X, because he's the only player to have played there as MAX(VALUE) == MIN(VALUE) and that COUNT(VALUE) == 3 (where three is the number of tiles in the win condition)

Unfortunately, I don't know SQL syntax good enough to expand this into SQL procedures and stuff, I will leave that up to you to do if you wish :)

StackExchange Code Review Q#54856, answer score: 6