I download stats and information around 3-4 times a year from a site. It's players, teams, nations etc., and lots of stats link to each all categories. I store the information in some different tables in my database, such as:
So far so good. But then an update comes. The easiest solution would probably be to simply replace the above tables with the new updated stats. But the problem is I want to keep these stats for future use (like graphic development of the individual stats).
What's the best solution to this? The only thing I have come up with is to create a new set of separate tables. For example, year 12 version one could be:
And then when the next update comes, and the next after that, it would look like this:
(I would need some kind of dynamic tablename query here as well, but I wait with that question in case of the above is a very bad way.)
Now, it will be many tables...but I don't know how else to keep the information. Of course I could simply INSERT the new update, and have a column named the update (12_1, 12_2 etc.), but the problem is that a PRIMARY for the playerid won't be a PRIMARY anymore...
you wouldn't have all of that in the players table, right?
Oh yes, that's true. Well, I could divide the stats that are history-based and the ones that are more profile-based. As it currently is, the file I have contains all stats linked to the player, for example contract length, height, weight, rating, reactions, stamina, strength etc.
That way I can have one table with unique player-ids and their profile, and one with the players stats. When new updated stats then appear, these can be inserted into the player stats table with a date-column or something similar.