[dba-Tech] Questions about 2 Unusual Databases

Shamil Salakhetdinov shamil at users.mns.ru
Wed Oct 27 13:39:50 CDT 2004


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...



----- Original Message ----- 
From: "Arthur Fuller" <artful at rogers.com>
To: "Discussion of Hardware and Software issues"
<dba-tech at databaseadvisors.com>
Sent: Wednesday, October 27, 2004 9:09 PM
Subject: [dba-Tech] Questions about 2 Unusual Databases


> From time to time I ponder the following two databases, trying to come
> up with the optimal design in terms of both space and performance. This
> is strictly a question of personal interest, and I have no commercial
> interest in either solution. I simply find them interesting problems,
> and I thought I'd trot them out in search of feedback from my colleagues
> here.
>
> 1. A database that records chess games. It strikes me that perhaps the
> most compact way to store a game is by using the modern notation for the
> moves themselves. But in addition to recording the sequence of moves,
> the database would also be expected to record situations and be able to
> compare them. I.e. given two sequences, A and B, that both result in
> exactly the same position of pieces, irrespective of the number of moves
> it took to get there, the database should be able to detect this as
> quickly as possible. For example.... aha! This is exactly the same
> position that Bobby Fischer faced in year 19xx, when playing somebody at
> some tournament, but they got here in 11 moves and the current players
> got here in 13 moves. (The idea behind this requirement is that certain
> positions have known solutions, i.e. paths to checkmate.)
>
> 2. A music database that records (let's keep it simple in version 1)
> melodies and single-line compositions (i.e. ignoring instrumentation,
> harmony, counterpoint, etc.). The idea here would be to compare any two
> rows and determine whether they are identical. For example, George
> Harrison v. the Ronnettes, for "My Sweet Lord" and "He's So Fine"
> respectively. Ideally, this database should also be able to see past the
> selected key (in the musical sense), and also the tempo (piece A is
> identical to piece B but played twice as fast). Perhaps version 2 could
> also detect that melody A is identical to B except that it is inverted
> (upside down) or perhaps retrograde (backwards) or even retrograde
inverted.
>
> Ok, database designers. There you have the specs. Any brilliant ideas
> out there for solutions?
>
> A.
>
> P.S.
> Although these are in fact strictly database issues, I am not going to
> cross-post to the AccessD and SQL lists because they are so obviously
> unrelated to the immediate problems most of us have when posting there.
>
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com




More information about the dba-Tech mailing list