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

[Holger]

At 17:48 03.09.2003 +0000, Joern Thyssen wrote:
> On Tue, Sep 02, 2003 at 08:18:24PM +0200, Jim Segrave wrote
>
> > Table: Matches/Sessions
> > ID playerID playerID  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

Maybe we could add this as an auxiliary table for lookup, if the below is 
too slow.

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

Or:
SELECT ...
FROM match/sessions
WHERE (player1 = p1 && player2 = p2) || (player1 = p2 && player2 = p1)

I don't know which is faster, though.

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

I'm not sure whether we should leave that one out. I believe the layout has 
to be complete and final before anybody could start using it. Else all 
importing of data had to be redone. Therefore, I suggest that we discuss 
the layout better extensively before starting to code.

I'll start a survey in another thread for all (im)possible queries. Then we 
can check whether the current layout would match or need to be extended.

Regards,

        Holger