creating advanced? table relationships

Hi, all,

I am creating a db for a school basketball league. I want to track team
stats per game, students stats per game, and games played.

I have the following tables and primary and foreign keys:
students-
pk: studentID fk: teams

studentGameStats (student stats per game)-
pks: studentID/GameID

gameDetails (game date, time, place)-
pk:Game ID

teams-
pk: teamID

teamGameStats(teams stats per game)-
pks: teamID/GameID

I have the game ID which can keep track of the student stats per game in one
table and the team stats in another table but I'm trying to relate the
student stats table and the team stats table so that I can show a team and
the players on the team for any particular game. I'm having a problem
relating these tables because they create a many to many relationship. How
can I resolve this issue and what would be the primary key(s).

Thanks for reading. Any suggestions would be appreciated.
AEA


(thought I posted this previously, but can't find it)

Odd. I answered this question for you yesterday. My answer shows up in
Google

http://groups.google.com/group/micro...e8a651d7c30d71

but when I try to look at it on this server, it's marked as no longer
available.

Here's what I said yesterday:

Based on your description, I don't actually see a many-to-many relationship
between studentGameStats and teamGameStats, unless you're worried about
players changing teams during the seaon (but that would be handled as a
many-to-many between studetns and teams)

To be honest, I don't see the need for teamGameStats at all: shouldn't you
be able to derive it by summing the stats for each player on the team?


In any case, many-to-many relationships are resolved by introducing an
intersection entity that has the PKs of the two related tables. Take a look
at the Northwind database that comes with Access. The relationship between
Products and Orders is a many-to-many (a product can exist on many orders,
and an order can contain many products). The intersection entity there is
the Order Details table. For an example of how to handle updates, see the
Orders and Orders Subform forms.



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AEA" wrote in message
...
Hi, all,

I am creating a db for a school basketball league. I want to track team
stats per game, students stats per game, and games played.

I have the following tables and primary and foreign keys:
students-
pk: studentID fk: teams

studentGameStats (student stats per game)-
pks: studentID/GameID

gameDetails (game date, time, place)-
pk:Game ID

teams-
pk: teamID

teamGameStats(teams stats per game)-
pks: teamID/GameID

I have the game ID which can keep track of the student stats per game in
one
table and the team stats in another table but I'm trying to relate the
student stats table and the team stats table so that I can show a team and
the players on the team for any particular game. I'm having a problem
relating these tables because they create a many to many relationship. How
can I resolve this issue and what would be the primary key(s).

Thanks for reading. Any suggestions would be appreciated.
AEA


(thought I posted this previously, but can't find it)

=?Utf-8?B?QUVB?= wrote in
:

I am creating a db for a school basketball league. I want to track
team stats per game, students stats per game, and games played.


I worry about table names like GameStats... this seems to me to be a
program function rather than an entity. I don't know whether this schema
is similar to the one you have but the semanticss are perhaps clearer:

Students(*PersonID, FName, LName, YearOfBirth, etc)

Games(*GameNumber, DateTimeOfGame, Venue, FinalScore)

Teams(*TeamCode, FullName, Owner, etc)

TurnOuts(*GameNumber, *HomeOrAway, TeamNumber)
FK GameNumber references Games
FK TeamNumber references Teams
// note HomeOrAway can take one of two values
// note this model which teams turned out for each game

PlayedIn(*GameNumber, *HomeOrAway, *PersonID, TriesScored, _
TotalPlayingMinutes, InjuryType, etc)
FK (*GameNumber, *HomeOrAway) references TurnOuts
FK PersonID references Students
// note This table will presumably model most of your
// players' stats etc
// note There is NO foreign key on GameNumber, since it's
// implicit and I don't think that Access is smart enough
// to notice.

This allows complete movement of players from one team to another, which
seems to be a requirement.

Without thinking about it very hard, it may be that the Games.FinalScore
would be better replaced by two values stored in TurnOuts records.

Hope that helps


Tim F

Thanks for your response. It is odd that my first post disappeared, yet
appears in google.
Anyway, you are absolutely right about getting team stats from the student
data. That makes so much sense.
Now my issue is how do I match the teams that played with the players that
played for that particular game? Should I put the team names in the
gameDetails table?

Thanks.
AEA


"Douglas J. Steele" wrote:

Odd. I answered this question for you yesterday. My answer shows up in
Google

http://groups.google.com/group/micro...e8a651d7c30d71

but when I try to look at it on this server, it's marked as no longer
available.

Here's what I said yesterday:

Based on your description, I don't actually see a many-to-many relationship
between studentGameStats and teamGameStats, unless you're worried about
players changing teams during the seaon (but that would be handled as a
many-to-many between studetns and teams)

To be honest, I don't see the need for teamGameStats at all: shouldn't you
be able to derive it by summing the stats for each player on the team?


In any case, many-to-many relationships are resolved by introducing an
intersection entity that has the PKs of the two related tables. Take a look
at the Northwind database that comes with Access. The relationship between
Products and Orders is a many-to-many (a product can exist on many orders,
and an order can contain many products). The intersection entity there is
the Order Details table. For an example of how to handle updates, see the
Orders and Orders Subform forms.



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AEA" wrote in message
...
Hi, all,

I am creating a db for a school basketball league. I want to track team
stats per game, students stats per game, and games played.

I have the following tables and primary and foreign keys:
students-
pk: studentID fk: teams

studentGameStats (student stats per game)-
pks: studentID/GameID

gameDetails (game date, time, place)-
pk:Game ID

teams-
pk: teamID

teamGameStats(teams stats per game)-
pks: teamID/GameID

I have the game ID which can keep track of the student stats per game in
one
table and the team stats in another table but I'm trying to relate the
student stats table and the team stats table so that I can show a team and
the players on the team for any particular game. I'm having a problem
relating these tables because they create a many to many relationship. How
can I resolve this issue and what would be the primary key(s).

Thanks for reading. Any suggestions would be appreciated.
AEA


(thought I posted this previously, but can't find it)

Not the names, but certainly the TeamIds. Presumably one team would be Home
and one Visitor.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AEA" wrote in message
...
Thanks for your response. It is odd that my first post disappeared, yet
appears in google.
Anyway, you are absolutely right about getting team stats from the student
data. That makes so much sense.
Now my issue is how do I match the teams that played with the players that
played for that particular game? Should I put the team names in the
gameDetails table?

Thanks.
AEA


"Douglas J. Steele" wrote:

Odd. I answered this question for you yesterday. My answer shows up in
Google

http://groups.google.com/group/micro...e8a651d7c30d71

but when I try to look at it on this server, it's marked as no longer
available.

Here's what I said yesterday:

Based on your description, I don't actually see a many-to-many
relationship
between studentGameStats and teamGameStats, unless you're worried about
players changing teams during the seaon (but that would be handled as a
many-to-many between studetns and teams)

To be honest, I don't see the need for teamGameStats at all: shouldn't
you
be able to derive it by summing the stats for each player on the team?


In any case, many-to-many relationships are resolved by introducing an
intersection entity that has the PKs of the two related tables. Take a
look
at the Northwind database that comes with Access. The relationship
between
Products and Orders is a many-to-many (a product can exist on many
orders,
and an order can contain many products). The intersection entity there is
the Order Details table. For an example of how to handle updates, see the
Orders and Orders Subform forms.



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AEA" wrote in message
...
Hi, all,

I am creating a db for a school basketball league. I want to track
team
stats per game, students stats per game, and games played.

I have the following tables and primary and foreign keys:
students-
pk: studentID fk: teams

studentGameStats (student stats per game)-
pks: studentID/GameID

gameDetails (game date, time, place)-
pk:Game ID

teams-
pk: teamID

teamGameStats(teams stats per game)-
pks: teamID/GameID

I have the game ID which can keep track of the student stats per game
in
one
table and the team stats in another table but I'm trying to relate the
student stats table and the team stats table so that I can show a team
and
the players on the team for any particular game. I'm having a problem
relating these tables because they create a many to many relationship.
How
can I resolve this issue and what would be the primary key(s).

Thanks for reading. Any suggestions would be appreciated.
AEA


(thought I posted this previously, but can't find it)

Thank you for responding. This is great. I didn't realize that I could create
a relationship with more than one pair of primary and foreign keys. I noticed
the extra lines in the edit relationships dialogue box, but never gave them a
second thought. (never came across this in the books I read)

Thank you very much. I will certainly click the "Did this post answer the
question?" button.

AEA

"Tim Ferguson" wrote:

=?Utf-8?B?QUVB?= wrote in
:

I am creating a db for a school basketball league. I want to track
team stats per game, students stats per game, and games played.


I worry about table names like GameStats... this seems to me to be a
program function rather than an entity. I don't know whether this schema
is similar to the one you have but the semanticss are perhaps clearer:

Students(*PersonID, FName, LName, YearOfBirth, etc)

Games(*GameNumber, DateTimeOfGame, Venue, FinalScore)

Teams(*TeamCode, FullName, Owner, etc)

TurnOuts(*GameNumber, *HomeOrAway, TeamNumber)
FK GameNumber references Games
FK TeamNumber references Teams
// note HomeOrAway can take one of two values
// note this model which teams turned out for each game

PlayedIn(*GameNumber, *HomeOrAway, *PersonID, TriesScored, _
TotalPlayingMinutes, InjuryType, etc)
FK (*GameNumber, *HomeOrAway) references TurnOuts
FK PersonID references Students
// note This table will presumably model most of your
// players' stats etc
// note There is NO foreign key on GameNumber, since it's
// implicit and I don't think that Access is smart enough
// to notice.

This allows complete movement of players from one team to another, which
seems to be a requirement.

Without thinking about it very hard, it may be that the Games.FinalScore
would be better replaced by two values stored in TurnOuts records.

Hope that helps


Tim F

Thank you for your help, especially the info about "destroying" (my word) the
team stats table. I was trying to get all my team info represented in the
tables, not realizing I could do it in a query or other object like the
student games averages.

I can stop banging my head against the monitor now. My monitor thanks you
too.


Not the names, but certainly the TeamIds. Presumably one team would be Home
and one Visitor.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AEA" wrote in message
...
Thanks for your response. It is odd that my first post disappeared, yet
appears in google.
Anyway, you are absolutely right about getting team stats from the student
data. That makes so much sense.
Now my issue is how do I match the teams that played with the players that
played for that particular game? Should I put the team names in the
gameDetails table?

Thanks.
AEA


"Douglas J. Steele" wrote:

Odd. I answered this question for you yesterday. My answer shows up in
Google

http://groups.google.com/group/micro...e8a651d7c30d71

but when I try to look at it on this server, it's marked as no longer
available.

Here's what I said yesterday:

Based on your description, I don't actually see a many-to-many
relationship
between studentGameStats and teamGameStats, unless you're worried about
players changing teams during the seaon (but that would be handled as a
many-to-many between studetns and teams)

To be honest, I don't see the need for teamGameStats at all: shouldn't
you
be able to derive it by summing the stats for each player on the team?


In any case, many-to-many relationships are resolved by introducing an
intersection entity that has the PKs of the two related tables. Take a
look
at the Northwind database that comes with Access. The relationship
between
Products and Orders is a many-to-many (a product can exist on many
orders,
and an order can contain many products). The intersection entity there is
the Order Details table. For an example of how to handle updates, see the
Orders and Orders Subform forms.



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AEA" wrote in message
...
Hi, all,

I am creating a db for a school basketball league. I want to track
team
stats per game, students stats per game, and games played.

I have the following tables and primary and foreign keys:
students-
pk: studentID fk: teams

studentGameStats (student stats per game)-
pks: studentID/GameID

gameDetails (game date, time, place)-
pk:Game ID

teams-
pk: teamID

teamGameStats(teams stats per game)-
pks: teamID/GameID

I have the game ID which can keep track of the student stats per game
in
one
table and the team stats in another table but I'm trying to relate the
student stats table and the team stats table so that I can show a team
and
the players on the team for any particular game. I'm having a problem
relating these tables because they create a many to many relationship.
How
can I resolve this issue and what would be the primary key(s).

Thanks for reading. Any suggestions would be appreciated.
AEA


(thought I posted this previously, but can't find it)

=?Utf-8?B?QUVB?= wrote in
:

Thank you for responding. This is great. I didn't realize that I could
create a relationship with more than one pair of primary and foreign
keys. I noticed the extra lines in the edit relationships dialogue
box, but never gave them a second thought. (never came across this in
the books I read)

Glad it helped. FWIW, the fastest way to create a relationship is in the
Relationships window: just ctrl-click the foreign keys and then drag them
over to the related table. The GUI will do the rest.

B Wishes


Tim F