Re: Giant database schema confusion

[Bob Field]

Start with a board table. Each row in this table has a unique board number (primary key, auto_increment) and this ties into a positions table (board number, rank, file, piece, primary key board-rank-file) listing each piece present on the board.

Then the moves table can have two board numbers: one representing the position of the pieces at the start of the move, the other representing the position of the pieces after the move.

So far there is no way of uniquely identifying a board other than a long string of board pieces, but it might be worthwhile to find a way to describe a layout in a text string less than a thousand characters, store this as a unique key of the board table. A stored procedure or trigger could generate this code automatically from the positions table or vice versa.