I am building a database with football players info in, i have the core of the database setup but want to add a new feature, but have no idea how to do this.

I currently have the following:

code:
CREATE TABLE `players` (
`PlayerID` int(11) NOT NULL auto_increment,
`Forename` varchar(50) default NULL,
`Surname` varchar(50) NOT NULL default '',
`DOB` date default NULL,
`RegNo` tinyint(4) default NULL,
`Photo` varchar(255) default NULL,
`Nickname` varchar(50) default NULL,
`Comments` varchar(255) default NULL,
`Position` varchar(50) default NULL,
`GamesPlayed` tinyint(4) default NULL,
`GoalsScored` tinyint(4) default NULL,
PRIMARY KEY (`PlayerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;



i have no idea what this bit is a bout tho...

code:
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;



Anyway what i want to do is add a year function to my tables, so i can track the players who have registered in which season.

I am assuming i should have another table with the following in...

Season ID: auto increment (PK)
Season: (TEXT)2003/04, 2004/05, 2005/06, 2006/07 etc

but how would i link this to my current table

I was thinking of adding a new feild in my current table (checkbox) for each season and just ticking each player who is registered for each season, but i guess that isnt the right way to do things...

Here are three options that I can think of:

OPTION 1:
========
players (playerID, Forename, Surname..., Season1, Season2, Season3...)

This is not "ideal" but may meet your purpose if you only have 1 season per year... definitely keeps it simple. Do not rule this out.

To get all players for a season you would use:
SELECT players.*
FROM players
WHERE Season1=1


OPTION 2:
========
players (playerID, Forename, Surname...)
player_seasons (playerID, seasonName)

To get all players for a season you would use:
SELECT players.*
FROM players
LEFT JOIN player_seasons on (players.playerID=player_seasons.playerID)
WHERE seasonName="2004/2005"


OPTION 3: (ultimate)
================
players (playerID, Forename, Surname...)
seasons (seasonID, seasonName)
player_seasons (playerID, seasonID)

To get all players for a season you would use:
SELECT players.*
FROM players
LEFT JOIN player_seasons on (players.playerID=player_seasons.playerID)
LEFT JOIN seasons on (seasons.seasonID=player_seasons.seasonID)
WHERE seasonID=3


I'd recommend either Option 1 or Option 3. Good luck!