Logic problem. Need help setting up stats database.

[BigToque]

This is posted in software as well, but this is a logic problem and not directly related to software.

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?

 

[AStar617]

for the potential size/complexity of the dataset, you wouldn't want a flat database even if it could be done with one. Multiple tables is the way to go. I can't help with joins etc. since I haven't touched a db backend in over 2yrs. Good luck tho.

 

[EagleKeeper]

Setup ech game with an unique ID.

Then have tables for each peice of accumulated information with a reference back to the original game via the ID.

Each players has a uniique ID

You will have a table that links the palyers with the game.

Then a table that links the stats for each player with the player ID and game ID.
The stats can be a total or a timestamped record for what ever the stat is.