Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010

    Database design question

    Hi everyone,

    I'm trying to build a database which holds records for football matches. The tricky part is that squad number should be unique within the team, so it can't be a primary key on it's own.

    I have 4 tables:
    tblTeam ( teamID, teamname)
    tblPlayers (lngPlayerSquadNumber ,lngTeamID ,name,position)
    tblFixtures(lngFixtureID ,date, time, hometeam,awayteam)
    tblPlayerFixtures (lngPlayerSquadNumber ,lngTeamID, lngFixtureID, intGoalsScored)

    I've added lngPlayerSquarNumber and lngTeamID as a composite key in the tblPlayers.

    Thus, the key for the table lngPlayerFixtures becomes a composite key of lngPlayerSquarNumber and lngTeamID and lngFixtureID, and that table contains 3 foreign keys.

    Is that a sensible way to do this? Do I need to add 3 foreign keys to this relation as well, and a relationship between tblPlayerFixtures and tblTeam?

    I tried it out in MySQL withouth a foreign key to the team table and it looked as if it worked.
    Any pointers will be appreciated. Thank you.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    wow, this is an extremely popular app at the moment, i wonder how many schools are using a variation on this... or maybe all the people posting this problem on discussion forums are trying to subtly disguise it by changing the table and column names...

    see | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Thank you for your reply. I can see that this is a popular topic however you didn't answer my question :P

    I was asking a question on design and you provided me with a link to a query related post. I wasn't looking for an answer, just a verification whether what I did is correct or not.

Posting Permissions

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