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.