I'm building my first ever web-based game in PHP/MySQL and I'am having difficulty conceptualising how to deal with saved game data instances. It's a football manager game with tables like...
players, managers, matches, results, injuries, stadiums, leagues etc
My problem is that some of those tables need to hold completely different data for each saved game and I don't know the best way to handle saving multiple instances of all game data.
What is the most elegant way to handle this? Should I create a totally unique set of transactional tables every time a new game is created? e.g. if I have 1000 games, they're be 1000 different variations of the "results" table? The only other rather ugly database-design solution I can think of doing is having a game_id column in every table that varies game-by-game.
Or is there some other technology outside of the database layer that better caters for coping with a potentially large number of different instances of the same data? The best match I could find for this question on StackOverflow was How do games handle saved content?, but it's a pretty old post and doesn't completely address my problem. Thanks for any thoughts at all.