table structure

[fipp]

I have a database set up with information pertaining to football games.

I have a player table and as you would assume that information is all about
the individual players. (names etc...)

I have a play table. The play table is information about each play in a
game.(down, distance, time on clock, etc...)

Now I would like to keep track of which player is playing which of the 11
positions on the field on each play. I am not sure weather I should add the
11 positions as fields in the play table? Should I add a new table and link
play and player? If I create a new table should the fields be position and I
type the position in and link the player name or should the fields be pos1,
pos2, etc...?

The last thing that I would like to add to it is on any given play there may
be some stats tied to each player for example a tackle or an assisted tackle.
It is possible in the way we grade for more than 1 person to be credited with
the tackle or assisted tackle on each play.

 

[Dorian]

You need a table that identifies which players were on the field and in which
position at any moment in time.
Player
Position
From time
To time
Then when you define the plays, you select the time the play ocurred and
then can select which players were on the field based on the above table.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Paul Shapiro]

Or you could use playNumberFrom and playNumberTo instead of time. Position
should be a FK from a PositionType table.

For the play statistics, you would need another table so you could record
multiple players' contributions to that play and also multiple contributions
from a single player. For example, a player could cause a fumble, recover
the fumble and score. A FK from a ContributionType or maybe ActionType table
would be one of the attributes. The PK could be a combination of game,
playNumber, playerID, actionTypeCode.

 

[fipp]

I appreciate everyone's help.

If I were to create a table with position as a field and linked player via a
foreign key then the problem that I have is I would have to enter 11
positions and 11 players for every single play. Most of the time any given
set of players is on the field for 5 or so plays ina row. For those 5 plays
there are minimal substitution. After about 5 plays there is massive
substitution usually involving a special teams play lasting 1 play and then
massive substitution to the other side of the ball. I was originally thinking
that I could have a formfor each side of the ball and special teams and I
could make the adjustments to one of the positions on the form and it would
update the record in the field accordingly? Sounds like a simple problem and
it probably is to someone but I am still struggling with it.