table set up

[KRosier]

Hi folks! I'm still working on my Horseshoe DB (as I have time). I am
reposting this question as the original seems to have become buried.

Using Access 2000 and beginning VBA skills.

I have my tables set up (shown below) to keep track of scores and
standings, both per player and per team for a horseshoe league. 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.

If I add a SubTeamNo to the tblGameStats, it would be duplicating the
information (sort of) in the tblTeam. Is this how it should work or is
there a better table set up that I'm just not seeing? A good push in
the right directions would be very much appreciated.

Thanks in advance for your help and patience!

Kathy


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

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

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

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

 

[jacksonmacd]

It sounds to me like every game stat must be tracked individually.
Modify its stucture to include a TeamPlayingForID foreign key.

This will result in a table like:
tblGmStats
==========
GmStatsID (autonumber, key)
WkStatsID (linked 1-many to tblWkStats)
PlayerID
TeamPlayedForThisWeekID
GmNo
Score

Thus you know everything about every score for every player for every
game, including which team s/he played for in that particular game.

You can use Index-No Duplicate on various combinations of fields in
this table so that duplicates are prevented. For example, you would
not want one player to be recorded in the same game twice in a single
week.

Change your thinking about the TeamID in the tblPlayer table. It
should now become TeamNormallyPlayedForID (or similar, you get the
idea). For your substitute players, this field would be Null -- they
don't have a team that they normally play for. Be sure to change the
Default value of this field -- Access normally sets it to "0", but you
will want to delete the Default value. Its Required setting remains as
False.

(This idea also lets your database be portable from year-to-year, and
allow players to move between teams from one year to the next. That's
a good thing!)

To make it easy for data entry, your application program should create
empty records in the tblGameStats as required. For example, when
entering scores for a selected team, it should create records for all
the players who normally play for that team for all games for that
week. The user would just enter the score for each game. But that's a
*user interface* problem, not a *database design* problem.


Your tblWeekStats includes a PlayerID field -- I think that's a
mistake. What was your intention?

 

[KRosier]

First of all let me apologize for not getting back to this right away.
I'm hoping this won't be buried under a week's worth of posts.

Yes, all stats must be tracked individually, but also some on a WkNo
basis, or over the course of the season. I will need to be able to pull
(or calculate) the following from my tables:

Player
high game
high game w/ handicap
high series (Sum of WkNo/Score)
high series w/ handicap
average
handicap

Team
high game (Sum of team member's GmNo/Score)
high game w/ handicap
high series
high series w/ handicap
total points

Average and handicap will be calculated on a per WkNo basis
High games and a high series will be calculated over the entire season,
i.e. who has the highest game thus far
Wins and Losses are calculated on a GmNo basis (I'm saving this one till
I get the rest of the kinks ironed out. At this point the league
secretary manually enters wins/losses).

If I am reading your suggestion correctly, I should link the tblGmStats
directly to the tblPlayer, and have tblWkStats linked only to tblGmStats
(not to tblPlayer at all)? The reason I placed the PlayerID in the
tblWkStats was that I was using the "waterfall effect"... 1 team has
many players, 1 player plays many weeks, 1 week has many games. Can you
tell me the reason why it should be the way you suggested? (I'm not
questioning your knowledge, just trying to figure out the logic behind
it so that I can apply it to whatever project may come next.)

Also, if I put TeamPlayedForThisWeekID into the tblGmStats, wouldn't
that mean I would be entering redundant information each week for those
players that are members of a team (always on Team 4). I can see that
it would work great for subs, though. Or do you mean leave the tblTeam
and tblPlayers relationship set up as is, and add the
TeamPlayedForThisWeekID for subs only? As I was reading this for the
hundredth time I think it finally sank in, but could you please clarify it?

I knew about the Null Value setting, but I appreciate the reminder!

Thank you so much for your help, and especially for your patience.

Kathy

 

[jacksonmacd]

First of all let me apologize for not getting back to this right away.
I'm hoping this won't be buried under a week's worth of posts.

No appology necessary. My newsreader automatically downloads any
threads that I've participated in.

Yes, all stats must be tracked individually, but also some on a WkNo
basis, or over the course of the season. I will need to be able to pull
(or calculate) the following from my tables:

Player
high game
high game w/ handicap
high series (Sum of WkNo/Score)
high series w/ handicap
average
handicap

Team
high game (Sum of team member's GmNo/Score)
high game w/ handicap
high series
high series w/ handicap
total points

All those will be feasible. For every game, you will know the week,
gameNo, Player, and Team as well as the actual score. You can
summarize in any combination of those attributes.

Average and handicap will be calculated on a per WkNo basis
High games and a high series will be calculated over the entire season,
i.e. who has the highest game thus far
Wins and Losses are calculated on a GmNo basis (I'm saving this one till
I get the rest of the kinks ironed out. At this point the league
secretary manually enters wins/losses).

If I am reading your suggestion correctly, I should link the tblGmStats
directly to the tblPlayer, and have tblWkStats linked only to tblGmStats
(not to tblPlayer at all)? The reason I placed the PlayerID in the
tblWkStats was that I was using the "waterfall effect"... 1 team has
many players, 1 player plays many weeks, 1 week has many games. Can you
tell me the reason why it should be the way you suggested? (I'm not
questioning your knowledge, just trying to figure out the logic behind
it so that I can apply it to whatever project may come next.)

No -- the tblWkStats is used to store information about the *week*.
The player is not an attribute of the week. You would not describe the
week by talking about the player that belongs to that week...

One other thing I notice in the way you describe your tables: you say
things like "... link the tblGmStats directly to the tblPlayer..."
That's not the way I consider it -- instead, I would consider it in
terms like "... the Player and the Team are attributes of the
tblGmStats"

I then consider the "linkages" ("Relationships" actually) as
supplementary things that help control the integrity of the data.

Also, if I put TeamPlayedForThisWeekID into the tblGmStats, wouldn't
that mean I would be entering redundant information each week for those
players that are members of a team (always on Team 4). I can see that
it would work great for subs, though. Or do you mean leave the tblTeam
and tblPlayers relationship set up as is, and add the
TeamPlayedForThisWeekID for subs only? As I was reading this for the
hundredth time I think it finally sank in, but could you please clarify it?

Kinda, sorta. You *could* consider it to be redundant info for the
"team" players, but (IMO) it's the correct way to do it. For one
thing, it promotes consistency between the sub's and the team players.
The difference between a sub and team player is whether or not they
normally belong to a team. For the actual game-stats table, they are
treated exactly the same.

A second thing it does is allow a user to change teams at the end of
the season, and not to corrupt the previous year's data.

It *does* create an issue with how to copy the default information
from the team players into the tblGmStats table. It's not a real
problem -- just something to thing about how you are going to do it.

I knew about the Null Value setting, but I appreciate the reminder!

I raised the point not only because it is something that's feasible to
do, but also because it is what differentiates a team player from a
sub player. The only thing that defines a sub player is that he does
not have a home team. (In truth, this design also allows a team player
to act as a sub for a *different* team in any given week. That may or
may not be something that you want to allow.

Thank you so much for your help, and especially for your patience.

You're welcome. HTH