Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Adding a new table linking up?

    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...

  2. #2
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Arrow Three options...

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •