How to design a database that tracks sports player's positions

Question

I'm building a database for a sports (ultimate) tracking program. Basically, the "raw data" for the program will be the positions and IDs for all the players on the field at one time. But the way I'm currently doing it seems to me to be overly complicated.

The main table, games, has columns for 7-10 player IDs on each team, matched against the IDs contained in the players table. It also has a column for each player's position on the field. But this creates a table with over 28-40 columns!

I'm fairly new to designing my own database, so I could be missing something basic, but is there a simpler way to do this?

(For what I want to do with the data, I do need to have a record of each player's position at each timestamp)

Clarification: 'position' means physical location on the field, not the name of the player's position

Answer 1

Sounds like your design isn't quite right. You probably need it like this (I imagine this is for football?):

Team Table - TeamID, TeamName...

Player Table - TeamID, PlayerName, PlayerPreferredPosition...

Position Table - PositionID, PositionName, PositionType (backs/mid/forward/Goalkeep etc)

Game Table - GameID, GameLocation, HomeTeam (FK Team), AwayTeam (FK Team), MatchStartTime...

GamePlayPosition - GameID (FK Game), PlayerID (FK Player), PositionID (FK Position), TimeStart, TimeEnd

That way you can figure out what players are playing at what position in what match at any time.

EDIT: If you want to use a grid and indicate exactly the time that player is spending there, I would suggest the following change, but what is above is still correct.

To the GamePlayPosition table add a surrogate key - GamePlayPositionID, use as your primary key. Then add another table:

ExactPlayPosition - GamePlayPositionID (FK), XPosition (small int), YPosition (small int), GameTime (datetime? Possibly an integer representing the seconds elapsed in the game time, easier to compare relative position game to game without doing datetime calculations if done with gametime).

The reason you have another table like this is because it will have a HELL of a lot of data, you want it as small as possible in terms of row width. You will be adding probably 3600 (# seconds in match) multiplied by 10 (# players in match) = 36k records per match, minimum if you don't include breaks. If tracking both teams of players, say 75k records per match, say over 50 matches... you get the idea.