Hello,

I am working on a very basic online chess game, similar to the chess game on Yahoo! Games. I am in the initial stages and planning out the schema and have a quick question.

Basically, there is a lobby with a list of users. A user will be able to connect to another user in the lobby and begin a new game in a new window. This new game window will have stats such as game time, scores, etc. This window will also have a history table on the side that displays the move history for the current game. So if a player moves a2a4 it will appear in the sidebar, and if the opponent moves to a new coordinate will appear under the previous move. Very similar to the Yahoo! Games version and most computer chess games. Once the game is complete the history would not be necessary anymore and be deleted.

I currently have the users listed in the lobby in a big table under “users”. When a player chooses an opponent to compete against, the player gets removed from the “user” column and inserted into the “opponent” column next to their opponent. Once the two players are in the same row, the remaining columns in that row contain the stats of the game, but I am not sure how to implement the move history data.

What would be the best way to implement the move history data?

1. For each new game that gets created, should a new history table get created, and then deleted once the game completes? I am not sure if this is safe method as I would not have much control for how many history tables will be created and their size.

2. Should I make a column in the lobby table called “history” and append each move after the entry into the cell? i.e. a2a4, b2b4, e3e5 would all be in one cell.

3. Create a table called history and have user names as each column, and whenever a player moves, their move is recorded in a new row under their name.

Perhaps there is a better completely different method, these were just the solutions I could think of…

Thank you in advance.

option 3 is heading in the right direction, except there are only two user columns, containing the user ids or names of the two players, as well as a game identifier and move identifier -- these four will make up the key of the game_moves table

i want to cede the rest of the response to blindman, who will give a much better answer than i could hope to, as he is a serious chess player

Please excuse my poor drawing skills...
Essentially is this what you mean?

game_moves_1
-----------------------------------------------------
|..move_id....|....r937....|....blindman....|.... game_id...|
-----------------------------------------------------
|.......1........|...a2a4...|.......c7c5.......|.. ......1........|
-----------------------------------------------------
|.......2........|...a4a5...|.......h7h5.......|.. ......1........|
-----------------------------------------------------
|.......3........|...d2d3...|.......h5h4.......|.. ......1........|
-----------------------------------------------------

And for each new game between two users, a new table would be made? (i.e. game_moves_2, game_moves_3, ...)

no, because you have "r937" and "blindman" as column names, and because you anticipate a new table for each game

i'm saying there would be one table for all games, and one table for all moves

as you can see, in game no 2 it is panno's move

That is an absurd suggestion. I never play the Sicilian.

If your application needs to access the move history, it would be best to store it in a table with columns such as:

GameID
MoveNumber
Color
Move

But if you don't actually need to reference individual moves as part of your application, then store the move list as a single text field in PGN (Portable Game Notation) format. The players might appreciate a PGN printout at the end of the game anyway, especially if the moves are not saved for them.

Haha, very funny blindman.

I see what you both are saying, the way you described makes a lot more sense to me now. Thank you sincerely for your help and input guys. You both saved me a multitude of potential headaches down the road. Have a good one!