Scrabble Game Score Database

[GSCaupling]

I'm trying to build an Access 2007 database to analyze Scrabble scores.

I have three tables with these fields:

Games: GmDate, GmNo, and NoPlayers
Scores: GmDate, GmNo, Player, Order, Score, HiScore
Players: Player

GmDate and GmNo would together be primary key.

I plan a form (games) and subform (scores).

Note: "GmNo" is the simply 1, 2 or 3 to represent whether it's the 1st, 2nd or 3rd game of that round. "Order" is the player's order of play, i.e., 1st, 2nd, 3rd or 4th.

I'd like to be able to analyze the data various ways: individuals against one another, whether there's an advantage in going 1st, 2nd,... 4th, change in performance from first to game to third, and of course individual stats.

Is this design on the right track? Also, is the NoPlayers field necessary?

Thanks

 

[fneily]

Question: you state "the 1st, 2nd or 3rd game of that round." Can there be more then one round in a day? If so, then using GmDate/GmNo won't work for a primary key. You only have three possibilites: GmDate1, GmDate2, GmDate3. That would be only one round. So you have to account for the round.

 

[JBinQLD]

I'd suggest:

Games: GmID (AutoNo), GmDate, GmNo
Scores: ScoreID, GmID, ScoreDate, PlayerID, Score
Players: PlayerID
GameOrder: GameOrderID, GmID, PlayerID, Order

From this you can query to work out number of players, each players highest score per game, the dates they played, etc.

And you're storing no redundant data

JB

 

[GSCaupling]

Thank you both for your replies.

Fneily: I had considered the possibility of more than one round per day, but history indicates it would be very unlikely. If it occurs, I guess I thought I'd just call it Game Number 4,5,6... (I realize this would negate a comparison of performance by "game per round" unless I exclude any GmNo >3 from the analysis).

JBinQLD: Besides GmID, are any other of your "ID" fields supposed to be Autonumbers? I thought for Players I would just use their names. Of course, it's late and my brain went to bed early!

Thanks again.

 

[JBinQLD]

ScoreID, PlayerID and GameOrderID would all be autonumber. The players name isn't a good key as it has the potential to violate 3 important rules of selecting a PK.

Uniqueness: You may have two David Jones's wishing to play

Stability: Miss Smith may meet the man of her dreams, marry, and become Mrs Jones

Simplicity: Strive to make your PK as short and simple as possible. Access provides you with an Autonumber field to produce short, effecient unique numbers. Using that makes the most sense (in my humble opinion)

And I presume you noticed I removed "Order" from your score table and put it into its own table. This is because if Fred is player 1 in Game 1, this will remain static through the life of the game and therefore it doesn't need to be recorded next to every of his scores.

Likewise, I removed Hiscore from the score table as this can be simply calculated at any point so again, storing it is pointless.

Hope this helps,


JB

 

[lespaul]

you may want to check out The Fundamentals of Relational Database Design...this is one of the basic documents on databases and the rules for designing them correctly

HTH

Leslie

Have you met Hardy Heron?

 

[GSCaupling]

Thanks again.

JBinQLD: Your second reply made me realize my initial post wasn't clear on a couple of things.

I'm not actually recording the score from each player's individual turns within the game whereby the entire game could be recreated. I was only going to record the end-of-game totals for each player. As a bonus bit of info, I was going to record each player's highest single turn score within each game - this was what I meant by HiScore (i.e., it can't be calculated). I think I'll change the name to HiTurn.

A typical game, with three players, has 10-13 turns per player. Game totals per player typically range from 130-275 points, and individual turns typically range from 8-60 points (my personal bests are 122 for a turn and about 320 for a game).

Sorry for the confusion, and thanks for the help. On top of everything, I'm just now trying to get used to Acc2007 after years using the last version.