Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Tables design help

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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