look up table

[shaddf]

I have this player registration table

 select player_id,Fname,Lname,Gender,Shirt_no FROM player_details;
+-----------+---------------------------+-----------------------------+--------+----------+
| player_id | Fname                     | Lname                       | Gender | Shirt_no |
+-----------+---------------------------+-----------------------------+--------+----------+
|         1 | Manager                   |  First-team coach           | NULL   |     NULL |
|         2 |  Goalkeeping coach        |  Fitness coach              | NULL   |     NULL |
|         3 |  Club doctor              |  First-team physiotherapist | NULL   |     NULL |
|         4 |  Masseur                  |  Assistant kit manager      | NULL   |     NULL |
|         5 |  Performance nutritionist |  Assistant manager          | NULL   |     NULL |
|         6 |  Head of performance      |  Head of medical services   | NULL   |     NULL |
|         7 |  Assistant fitness coach  | Under-21s assistant coach   | NULL   |     NULL |
|         8 |  Kit manager              |  Equipment manager          | NULL   |     NULL |
|         9 |  Football analyst         |  Academy manager            | NULL   |     NULL |
|        10 | Mulan                     | Babu                        | M      |       23 |
+-----------+---------------------------+-----------------------------+--------+----------+
10 rows in set (0.00 sec)

 and this summary table
 

select player_id,Team_catId,SeasonID from Soka_players_team_tbl;
+-----------+------------+----------+
| player_id | Team_catId | SeasonID |
+-----------+------------+----------+
|         1 |          1 |       12 |
|         2 |          1 |       12 |
|         3 |          1 |       12 |
|         4 |          1 |       12 |
|         5 |          2 |       12 |
|         6 |          2 |       12 |
|         7 |          3 |       12 |
|         8 |          3 |       12 |
|         9 |          3 |       12 |
|        10 |          4 |       12 |
+-----------+------------+----------+

if the seasonid changes,I have to reenter all the playeridsfor a particular teamcategory  for every new season.
for instace i will have to repeat ids 1-10 for a new seasonid 13.
Iam afraid this will make the table grow very huge for all the different team categories,considering  the seasons have to change,and i have to tell the players i had in a previous season
how can i redesign this to avoid this in the future??

[mac_gyver]

Iam afraid this will make the table grow very huge for all the different team categories,considering  the seasons have to change,and i have to tell the players i had in a previous season

 

 

calculate how long it will take for your table to have 5 million rows in it. with today's server hardware, 5 million rows in a properly indexed table is about the point where you need to start worrying about doing things like using partitioning or archiving older data.