[dba-Tech] Questions about 2 Unusual Databases

Shamil Salakhetdinov wrote:

>Arthur,
>
>I will dare to advise on the chess database design.
>Using KISS principle & if we assume you don't care to waste some space &
>that I didn't miss something here is a possible solution :
>
>- position after every move can be represented using 32 bytes(4bits * 64);
>- you put position information(code) into binary field and create an index
>on it so you'll have something like:
>
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[tblChessGame]') and OBJECTPROPERTY(id, N'IsUserTable') =
>1)
>drop table [dbo].[tblChessGame]
>GO
>
>CREATE TABLE [dbo].[tblChessGame] (
> [ChessGameId] [int] NOT NULL ,
> [ChessGameMoveNo] [smallint] NOT NULL ,
> [ChessGamePositionAfterMove] [binary] (32) NOT NULL
>) ON [PRIMARY]
>GO
>
> CREATE  INDEX [IX_PositionMoveNo] ON
>[dbo].[tblChessGame]([ChessGamePositionAfterMove], [ChessGameMoveNo]) ON
>[PRIMARY]
>GO
>
>
>- by using IX_PositionMoveNo index you'll quickly find identical positions
>happened on the same or on different MoveNo;
>
>select * from tblChessGame where (
> [ChessGamePositionAfterMove] =
>0x0123456789012345678901234567890123456789012345678901234567890123)
>
>or
>
>select * from tblChessGame where (
> [ChessGamePositionAfterMove] =
>0x0123456789012345678901234567890123456789012345678901234567890123
>  and ChessGameMoveNo = 35)
>
>(the positions codes above are only for example of SQL expressions - real
>codes will never look something like that - they will have at least 32
>zeros)
>
>- then you can quickly compare the bits and find for the case where there
>were the same quantity of moves were all the positions of these moves the
>same or different...
>
>Does it looks good for you?
>
>Shamil
>
>P.S. If you'll care about saving some space then you can pack positions'
>info - then the max quantity of bytes needed to represent a position will
>be:
>
>5*32+32 = 192/8 = 28 bytes,
>
>min = 8 bytes - empty chess board....
>
>Does it makes sense to complicate the life this way? - I don't think so...
>  
>
Thanks for responding, Shamil! I'll have to read your response over once 
or twice before I'm able to reply, but at first blush it looks 
promising. OTOH while I can see the normalization advantages to 
recording any given game as N rows each, there is something that I find 
quite compelling about storing the entire sequence as a varchar() string 
consisting of the modern notation's sequence of moves, rather than 
recording N rows one per move.

Since posting, I have done some Googling and have come up with some 
sites dedicated to chess notation. Since I have just begun to address 
this problem, I will automatically defer to their experience, but with 
the usual provisos. A couple of things I picked up worth noting:

In algebraic notation, a capture is recorded as AxB (i.e. Bishop takes 
knight). Where it is ambiguous, then the notation might be qualified by 
a "Hungarian" prefix, such as QBxK -- this assumes that both bishops 
could take a knight, and to disambiguate the move we specify that it's 
the Queen's bishop.

This notation seems to me foolish. Notating the capture itself is IMO 
redundant. The only way piece X can go to square Y is by taking the pawn 
currently occupying Y, so why bother notating the capture?

I really like your notions of compaction etc. but I fear that the only 
way that I can actually test them is by banging in a few games's moves 
one by one and then seeing what happens.

Something you didn't address that I would really like your opinion on is 
the "teleology" approach. Assume the Book of Endings is coded, and that 
player Shamil at move 22 is presented with situation X, which is only 
two or three moves away from a known victory derived from the Book of 
Endings. The unintelligent distance between the current position and the 
known position is huge, but the more intelligent analysis would somehow 
subtract all the similarities, note the differences and see if there's a 
way to get that knight over here and that rook over there, at which 
point the positions are identical and the victory guaranteed.

Thoughts?

A.