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

[Jim Segrave]

I talked to some of my developers today after work about how one might
organise a database for gnubg and the following was the suggestion
that initially came out. I was looking to make it flexible enough to
accomodate any strange query someone might come up with without making
the structure too complex. I think this more or less covers everything
except choutes, which gnubg doesn't handle anyway. But I'm not a DB
designer and the people who are and gave me some tips didn't spend a
long time on this, nor are they backgammon players, so there may be
some holes in it.

This database would grow fairly big if people store all games at the
move level, but one could probably easily arrange to not create the
move data (or drop the table after exporting a game/match/session), so
it's no big deal.

Comments, suggestions, laughter or recommendations to see a shrink are
all welcome, 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

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 decided to separate out a unique person identifier from their
nickname (which may change or may differ depending on where they are
playing), so you could choose to examine only matches you played
against Fred Flintstone playing as FFFred on Fibs (and ignoring the
matches you had with him on TMG) or all the matches agains Fred
Flintstone regardless of where you played him - although the latter
requires a more complex query if the same person uses several
nicknames. 

Matches and sessions can be described in a table, if a constraint is
added to ensure that the playerIDs of the opponents are ordered, then
you can easily get all the matches against a particular opponent. A
query for all matches involving a single player is again slightly
complicated by the need to look for that player ID in both places, but
it's no big deal. All of the match/session summary statistics go here

Games are found only in the context of matches or sessions and have
the per-game data 

Moves, for those who wish to keep that level of detail as opposed to
simply keeping the original .sgf files or whatever around, would have
the usual move type, dice and checquer/cube details. I was thinking of
also keeping the details of the best move if that was different from
the one played.

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



-- 
Jim Segrave           address@hidden