1

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

2

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.

  • I think I phrased the question bad. The "position" is not the player position (quarterback, left fielder, running back, shortstop), it is the physical location on the field, so a table will have hundreds or thousands of timestamps with recorded locations. – SSumner Jul 31 '14 at 11:51
  • Would it be better to insert multiple entries for a single timestamp, and just have one player column? (E.g. 20 entries each time, one for each player)? – SSumner Jul 31 '14 at 11:53
  • Oh OK. How you define the position? Do you split the field up into a number of grids (preferred method I would suggest, 1mx1m squares) or use exact geographic references? Plus tell me what your END goal is, what information do you want to get out of the system? How long each player spends in each position? A density graph of the field where the coloring depth indicates more/less time spent in that position? – blobbles Jul 31 '14 at 12:10
  • Oh and where will the data come from to record this? Tracking players by using a camera? Will be a horrendous task, and innaccurate, if doing it manually... – blobbles Jul 31 '14 at 12:24
  • It will use some form of Local Positioning System (so no, not manual). A number of things will come out of it - distance traveled, catches, throws, recreation of the game's positions for analysis. – SSumner Jul 31 '14 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.