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

[Jim Segrave]

On Thu 04 Sep 2003 (16:51 +0200), Holger wrote:
> At 20:18 02.09.2003 +0200, Jim Segrave wrote:
> 
> >Comments, suggestions, laughter or recommendations to see a shrink are
> >all welcome, some more than others.
> 
> I'll stick to the first two, although you quite provoke it. ;-)  And I had 
> to look up what a shrink is.
>
> >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
> 
> I'd like to add:
> ratings for both players
> maybe experience
> place where match was played, event, round, all other match information

Good point.

> >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
> 
> We should add the game number, imho anyway. I could imagine a few queries 
> for this. And with this the above ordering isn't obligatory.

Also a good idea.
It might also want to include a way to keep annotations. But Joern's
suggestion to get the other tables working first is probably a good
one.
 
> >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
> 
> Maybe, like above for games, a move count. This would make it possible to 
> look e.g. for opening moves.
> 
> Hey, your proposal is far from being crude. Or are you British, for the 
> understatement?

I cheated and got help from people who know something about using
databases. My original thinking was very much a functional
programmer's approach with the match table records specifying which games
records you need and the games table records specifying which move
records, etc.



-- 
Jim Segrave           address@hidden