Re: [Bug-gnubg] A crude proposal for a database schema

[Joern Thyssen]

On Tue, Sep 02, 2003 at 08:18:24PM +0200, Jim Segrave wrote

> Comments, suggestions, laughter or recommendations to see a shrink are
> all welcome, 

You shouldn't tempt us weak people :-)

> some more than others.
> 
> A proposed schema for an RDMS for backgammon games
> 
> Table: environment
> ID Places where nicks might be found (viz) Fibs, GamesGrid, TMG, Biba, Nebc
> 
> Table: people
> ID Name of person other details 
> 
> Table: player
> ID nickname environment-ID  people-ID
> 
> This allows for the likely possibility that on-line players on one
> site have identical nicknames to those used on another site by some
> totally different person
> 
> Table: Matches/Sessions
> ID playerID playerID  result(int) match/session(boolean) length(int)
>    date time
>    all relevant luck/cube/chequer/overall error rates

As you point out the two player IDs cause some trouble when searching
for matches for a given player.

(the following scribbles is just me thinking loud)

Modfied table: matches/sessions

ID result(int) match/session(boolean) length(int)
   date time
   all relevant luck/cube/chequer/overall error rates

New table: players in match
match/session ID  player ID

(the primary key is both attributes)

The new table would typically (read: always) hold two rows for each
match or session indicating the two players.

This makes it easy to find matches for a given player:

SELECT whatever
FROM matches/sessions'
INNER JOIN players in match
ON match ID
WHERE player ID = given ID


However, it gets more complicated to search for matches where two
certain players player each other

SELECT whatever
FROM matches/sessions'
INNER JOIN players in match pm1
ON match ID
WHERE player ID = given ID
AND exists ( SELECT '1'
             FROM players in match pm2
             WHERE pm2.match ID = pm1.matchID
             AND pm2.player ID = other given ID)

With your proposed scheme the queries would be

SELECT whatever
FROM match/sessions
WHERE ( player ID1 = given ID OR player ID2 = given ID )

and

SELECT whatever
FROM match/sessions
WHERE player ID1 = given ID AND player ID2 = other given ID.

If we drop the constraint on player ID1 and player ID2 being sorted the
last query will be

SELECT whatever
FROM match/sessions
WHERE player ID1 = given ID AND player ID2 = other given ID.
UNION
SELECT whatever
FROM match/sessions
WHERE player ID1 = other given ID AND player ID2 = given ID.

Hmm, it seesm that your scheme is better than my idea in the sense that
it makes easier queries...

Oh well, forget the above 65 lines :-)

> 
> Results are total points for money sessions +1/0/-1 for player 0 won
> match, match not complete, player 1 won match
> 
> Table: Games
> ID Match/sessionID result(int) rules(set Jacoby/Crawford) date time
>    opening-score0(int) opening-score1(int) is-Crawford(boolean) 
>    all relevant luck/cube/chequer/overall error rates
> 
> The assumption is that a select of all the rows in games with a
> specific Match/sessionID will give the games in the correct
> order. Opening scores would be session totals in money play, games
> away in match play (so you can easily select all your 2-away 4-away
> games for example) Result would be points to winner positive for
> player 0, negative for player 1
> 
> Table: Moves ID GameID PlayerID on roll Dice int(2) move int(8),
>     movetype enum (double, pass, drop, take, beaver, raccoon, normal,
>     set position), boardID, isbest (boolean), analysis stats
>     bestmovetype enum (double, pass...), bestmove int(8), analysis
>     stats

I'd say that we "drop" the Moves table for now, since I guess most
people would want to query game data rather than move data. Also, the
Moves table is the most complicated one. We'll probably learn a lot
implementing the other tables, so I think we should leave out the most
complex one for now.

> gnubg could write the records out easily (we'd need some method of
> filling in data like player name to nickname mappings and environments
> I want to change the GAME_INFO record to have a datestamp (I get
> annoyed if I play two matches against gnubg and analyse them that
> gnubg wants to overwrite the first matche's .sgf file).

Using IDs (env, player, match/session) requires us to have direct access
to the RDBMS so we can query the database for next "free" ID unless we
find some other way to generate unique IDs.

Jørn

pgpV6zrHlsWms.pgp
Description: PGP signature