logic problem... need help setting up player stats database

BigToque

Lifer
Oct 10, 1999
11,700
0
76
The way I have my hockey stats database set up, I can record all the stats for a player over a season and over multiple seasons. I just realized that this is not exactly what I want, so I need to redo the database.

What I want is to be able to record a list of players that played in a particular game, individual stats for each player during the game, and also keep track of what order the goals/assists happened.

Keeping stats on a game-by-game basis gives me an ability to do more with the data, and I can still keep track of season data as well.

I've been having a brain fart and I can't think of how to do this. Do I need multiple tables?
 

notfred

Lifer
Feb 12, 2001
38,241
4
0
Make a table for goals/assist that has fields: Game ID, Player ID, Time, Type(goal or assist). Put all the goals in there, select them by time to see what order they happened in.
For players in particular games, just make a PlayerInGame table with "player ID" and "Game ID" fields.select everything with a specific game ID to get all the players for that game.