Hello,

I am creating a web application to provide the form of football (soccer) matches. The problem I have is with the relationship between the tables TEAM and MATCH.

I have a table TEAM...

teamid (PK)
teamname

and a table MATCH...

matchid (PK)
hometeamid (FK relating to team.teamid)
awayteamid (FK relating to team.teamid)
hometeamgoals
awayteamgoals
matchdate

The relationship works fine but when it comes to caculating the amount of points for each team or the current league tables etc, the sql becomes quite complicated and a little cumbersome.

I would be grateful if anyone could suggest a more suitable, more efficient structure.

Thanks.

the sql is not complicated at all, you just need to join to the team table using an OR join condition

and that is the best table design