Table normalization and calculated fields

  • Thread starter Thread starter KRosier
  • Start date Start date
K

KRosier

Using Access 2000 with a beginner’s knowledge of VBA

I am trying to set up a database to track scores for a Horseshoe League.
(Scoring is very similar to a bowling league if that helps.) I have a
couple of questions on its design.

Currently I’m trying to decide if I need two or three tables for the
player info and stats. Here’s what I have:

tbl_player
playerID (key, autonumber)
Fname
Lname

tbl_stats
statsID (key, autonumber)
playerID (joined to tbl_player.playerID)
wk_no (week 1, week 2, week 3, etc.)
gm_date (date the games were played)
score1 (three games per series)
score2
score3


Two questions:
1) In tbl_gm_stats should I have one field for the scores and another
with the game number? There will never be more than three games per
week_no i.e.:
week 1, game 1, score; week 1, game 2, score; week 1, game 3, score
OR
week 1, score 1; week 1, score 2; week 1, score 3

2) Should I split the tbl_stats into two different tables as below

tbl_wk_stats
wk_statsId (key, autonumber)
playerID (joined to tbl_player.playerID)
wk_no
gm_date

tbl_gm_stats
gm_statsID (key, autonumber)
wk_statsID (joined to tbl_wk_stats.wk_statsID)
game_no (always 1, 2 or 3)
score

Then, once I get that set up, I’ll have three calculated fields
(handicap, high game w/handicap and high series w/handicap) that I think
I’ll need to store, which I know is generally frowned upon but I don’t
know how to do it otherwise.

Each week a handicap will need to be calculated upon all previous
scores, including the current week, and I will need a report with year
to date high game, high game w/handicap, high series and high series
w/handicap over the whole league – one high game per league, not high
game per player

The player handicap is based on the total of all previous scores and
will change from week to week. The high game and series w/handicap will
need to be able to be compared to previous week’s highs so that the
report can show the YTD high game and series (with and without handicap).

Should I be storing those calculated stats? I will want to be able to
look back over each week’s report and recalculating the stats each time
I want to see a different week doesn’t seem efficient.

Any help and ideas would be very much appreciated!

Kathy
 
See inline comments

Using Access 2000 with a beginner’s knowledge of VBA

I am trying to set up a database to track scores for a Horseshoe League.
(Scoring is very similar to a bowling league if that helps.) I have a
couple of questions on its design.

Currently I’m trying to decide if I need two or three tables for the
player info and stats. Here’s what I have:

tbl_player
playerID (key, autonumber)
Fname
Lname

tbl_stats
statsID (key, autonumber)
playerID (joined to tbl_player.playerID)
wk_no (week 1, week 2, week 3, etc.)
gm_date (date the games were played)
score1 (three games per series)
score2
score3


Two questions:
1) In tbl_gm_stats should I have one field for the scores and another
with the game number? There will never be more than three games per
week_no i.e.:
week 1, game 1, score; week 1, game 2, score; week 1, game 3, score
OR
week 1, score 1; week 1, score 2; week 1, score 3

2) Should I split the tbl_stats into two different tables as below

tbl_wk_stats
wk_statsId (key, autonumber)
playerID (joined to tbl_player.playerID)
wk_no
gm_date

tbl_gm_stats
gm_statsID (key, autonumber)
wk_statsID (joined to tbl_wk_stats.wk_statsID)
game_no (always 1, 2 or 3)
score

Then, once I get that set up, I’ll have three calculated fields
(handicap, high game w/handicap and high series w/handicap) that I think
I’ll need to store, which I know is generally frowned upon but I don’t
know how to do it otherwise.

Each week a handicap will need to be calculated upon all previous
scores, including the current week, and I will need a report with year
to date high game, high game w/handicap, high series and high series
w/handicap over the whole league – one high game per league, not high
game per player
Click to expand...

This is the key point to why you should create a separate table for
the game scores. Imagine trying to find the high-score with your
original three-field design -- you would have to check in three
different fields, but with a properly normalized structure, you need
to check only a single field.

Having established that you need a separate score table, you should
re-think whether you actually need a "game_no" field. It's only
purpose (IMHO) would be to indicate in *which* game a particular score
occured. My guess is that's not particularly significant, so I would
drop that field from the table.

You say that you would never have more than three games. Never is a
long time... A normalized structure would allow you to have 2, 3, 4 or
more games per week without changing the structure. I would value that
flexibility in the database design.

You could also eliminate the wk_no field because it can be readily
calculated from the gm_date.

As for you question about storing the handicaps -- no, they should be
calculated on-the-fly for presentation in your report. Calculating
them each time they are required may not seem efficient at first
blush, but it *is* the way to go, and Access will calculate it very
quickly if your database design is normalized and properly indexed.

Post back when you've made those changes and have additional specific
questions.
 
jacksonmacd said:
See inline comments



This is the key point to why you should create a separate table for
the game scores. Imagine trying to find the high-score with your
original three-field design -- you would have to check in three
different fields, but with a properly normalized structure, you need
to check only a single field.

Having established that you need a separate score table, you should
re-think whether you actually need a "game_no" field. It's only
purpose (IMHO) would be to indicate in *which* game a particular score
occured. My guess is that's not particularly significant, so I would
drop that field from the table.

You say that you would never have more than three games. Never is a
long time... A normalized structure would allow you to have 2, 3, 4 or
more games per week without changing the structure. I would value that
flexibility in the database design.

You could also eliminate the wk_no field because it can be readily
calculated from the gm_date.

As for you question about storing the handicaps -- no, they should be
calculated on-the-fly for presentation in your report. Calculating
them each time they are required may not seem efficient at first
blush, but it *is* the way to go, and Access will calculate it very
quickly if your database design is normalized and properly indexed.

Post back when you've made those changes and have additional specific
questions.
Click to expand...

Jack,
Thank you for your help and advice.

A bit of clarification:

In trying to keep my questions simple, I didn't mention the team aspect
of the database. game_no will be used when I move to the next step of
keeping track of team scores. I need to be able to add all of team1,
score1 together to get the team_high_game.

In regards to wk_no(week number) - think of it as an NFL football game -
the standings in week 3 are...such and such. Now, in football there
will only be one game played per week so using the gm_date would be
logical. However, in Horseshoes it is possible to play two 3-game
series in one day due to makeup games,etc. In that case the gm_date
would not work to sort on and I would need some other way to sort the
series, hence the wk_no.

I do see your point in making game_no flexible, though horseshoe scoring
hasn't changed in 20 years, one never knows. I'm quite sure there's
some Access law out there that says as soon as you finish your project,
something is sure to change, especially if you assume "never." ;-)

Please let me know if there is still a flaw in my logic.

Kathy
 
[snip]
Please let me know if there is still a flaw in my logic.

Kathy
Click to expand...

It all sounds logical to me. The additional requirements that you
omited for simplification in your first message make those fields
useful.
 
Okay, now I’m adding the team tables and need more help, or at least a
good shove in the right direction.

tblTeam
=======
TeamID (autonumber, key)
TeamNo
TeamName

tblPlayer
=========
PlayerID (autonumber, key)
TeamID (linked 1-many to tblTeam)
Fname
Lname

tblWkStats
==========
WkStatsID (autonumber, key)
PlayerID (linked 1-many to tblPlayer)
WkNo
GmDate

tblGmStats
==========
GmStatsID (autonumber, key)
WkStatsID (linked 1-many to tblWkStats)
GmNo
Score

This works great until I have subs. A sub can play for any team, in any
given WkNo (week number). And though highly unusual, it would be
possible for a sub to play for three different teams in a WkNo.

I need to be able to calculate team game totals and team series totals
both with and without handicap. No problem as long as the team members
remain the same. But how do I show a substitute player on team 1 the
first week and team 5 the second. Or worse, the sub on team 1 for the
first game of the first week and on team 3 for the second and third game?

Usually one player can be on only one team. But a sub can be on many
teams and I need to be able to differentiate which team he’s on for
which game and WkNo.

Thanks in advance for your help and patience!

Kathy
 
Back
Top