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.

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 Cant workout this query for the life in me!

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.