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