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

[Holger]

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

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

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

Regards,

        Holger