[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Bug-gnubg] A crude proposal for a database schema
From: |
Jim Segrave |
Subject: |
[Bug-gnubg] A crude proposal for a database schema |
Date: |
Tue, 2 Sep 2003 20:18:24 +0200 |
User-agent: |
Mutt/1.4.1i |
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
- [Bug-gnubg] A crude proposal for a database schema,
Jim Segrave <=