Posted by ZeldorBlat on 10/09/06 22:27

mick white wrote:
> I have a mysql table which gives results of matches played:
> mysql> desc PLG;
> +----------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+----------------+
> | matchid | int(10) unsigned | | PRI | 0 | auto_increment |
> | gamedate | date | YES | | NULL | |
> | homeTeam | varchar(30) | YES | | NULL | |
> | htg | smallint(2) | YES | | NULL | |
> | atg | smallint(2) | YES | | NULL | |
> | awayTeam | varchar(30) | YES | | NULL | |
> +----------+------------------+------+-----+---------+----------------+
>
> where htg = home team goals
> where atg = away team goals
>
> Is there a [simple*] way to create a league table?
> (using PHP 4, mysql 3.22.32)
> Any rhoughts?
> Thanks.
> Mick

First create a "team" table:

teamid
name

Change your matches table so that homeTeam and awayTeam are foreign
keys to the team table rather than the name of the team.

Now create a "league" table:

leagueid
name

Can a single team be in more than one league? If not, add a leagid
column to your team table. If a single team can be in multiple
leagues, create a leagueTeam table:

leagueid
teamid

If you have a row in there, it indicates that the team represented by
teamid is a member of the league represented by leagueid.

[Back to original message]