|
From: | Holger |
Subject: | Re: [Bug-gnubg] A crude proposal for a database schema |
Date: | Thu, 04 Sep 2003 17:15:53 +0200 |
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
[Prev in Thread] | Current Thread | [Next in Thread] |