Creating League Table

[leerobbo]

Alright all

 

I'm trying to create a league table from scratch using data from one table in my database. The table contains data about the matches such as the scores between who and etc. Using MySQL based on this data I have been able to get the league information for one team however there are numerous teams and the table obviously needs to be sorted by the team with the most points. I am aiming to provide data over various seasons so there will be more than one table, one set of matches and so on.

 

What would be the best approach for this? Should this be solely done within the webpage to display or should an individual table for the League table be created which could then display on the webpage.

 

Any help/advice will be appreciated.

 

Thanks.

[joel24]

have a relational DB with a table for the teams and another table for the results (including year, season etc)

 

i.e.

teams [id, name, city, etc, etc]

results [team1, team2, winner, score, season, year]

[leerobbo]

I thought about that however the problem that arose was how could the field in the teams table like name be related to both home and away as they will be playing home and away.

[joel24]

in the results table, have it set up like

results [

home (teamID from teams table)

away (teamID from teams table)

homeScore,

awayScore,

season,

year

]

[fenway]

Or, if you don't want to have annoying problems later, save each "side" of the score as a separate row, and tie them together with game ID, and flag as "home" or "away".

[leerobbo]

In the table for the matches data I do currently numerous fields which are storing information about the shots, shots on target etc and this includes fthg & ftag. They are the full time home goals and full time away goals which I think is what your suggesting?

 

So now link the team from the team table to the home and away fields and then it'd be performing queries on the column values when said team id is home/away?

[fenway]

I'm simply stating that you shouldn't store data for two team in one record.

[kickstart]

Hi

 

Further to Fenways comment, if you have a single row for the result of a match it makes finding a teams score more difficult as you need to join against either column.

 

Ie, if you have a table of teams

TeamId

TeamName

 

And a table of scores

ScoreId

HomeTeamId

AwayTeamId

HomeTeamScore

AwayTeamScore

 

If you want to know all the scores from matches a team has played you land up needing 2 queries unioned together:-

 

SELECT TeamName, HomeTeamScore, AwayTeamScore
FROM teams
INNER JOIN scores
ON teams.TeamId = scores.HomeTeamId
UNION
SELECT TeamName, AwayTeamScoe, HomeTeamScore
FROM teams
INNER JOIN scores
ON teams.TeamId = scores.AwayTeamId

 

If you have 2 rows on the score table for each match, one for each team you save the need for a UNION.

 

Another option is to create a view based on the scores table to return both combinations of each row.

 

All the best

 

Keith