In the game, hockey players have statistics on the seasons by several parameters: goals, passes, throws, etc. One hockey player has an average 10–15 year playing career, according to the game the season lasts ~ 2 months, i.e. Player's career lasts about 20-30 months. After that, all information about the player is removed from the database. At the moment, about 75 thousand players.

How to organize the storage of statistics in the database?

    3 answers 3

    The easiest thing that came to mind is to break all the stored information (goals, throws, etc.) in the tables by year. And make one table that will contain the names of the tables and the corresponding time intervals.

    Continuing the thought - depending on what kind of results should be obtained on the basis of statistics, they can also be calculated for a closed period and already stored, and not counted each time.

      In theory, the database structure should be such that championships , seasons , games , players, and statistics on players would be in separate tables. In the table with statistics (along with the most important parameters) there should be a connection by a foreign key with the tables of games and players, since It is according to the parameters of the player’s statistics in a particular game that the statistics for the game are built, both in the season and in the championship.

      Those. In my opinion, there is nothing wrong with storing all statistics in the context of players in one table.

      • one
        Yes. I would also recommend for the statistics table to make a unique key with auto-increment and clustered index, as well as the usual indexes for all keys. To optimize queries, you can still resort to some denormalization by dragging data into the statistics table that could be obtained by subqueries. But this should be only the data that is actually required to display to the user. - Modus

      The data about the players, and the results of the game as I understand it, are stored for a long time, so I offer the attached version below. Create tables. Data on the results of players stored in the table "Result of the game." Updating the table with application tools enter image description here

      • Please try to leave a little more detailed answers. You can add an answer by clicking edit - aleksandr barakin