ER diagram design

[kolokos]

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.

[Mestika]

There is no problem with having multiple foreign keys for an entity, but what you could consider is the relationship between your entities. If you got a many-to-many relationship then it is advisable to create a relationship between them with the foreign keys as attributes.

Remember that there is different on modeling and implementation so if your tblPlayerFixtures include the foreign key IngPlayerSquadNumber it can be derived from the tblPlayers entity.

You should try to get as lose coupling as possible within your relations but there is no flaw in having a relation that contains foreign keys from several of other entities. It just have to be as flexible as possible.