Need advice implementing this database

[Ballabriggs]

I'm supposed to be setting up a database for a local soccer league, I've been given this ERD

http://i.imgur.com/4q5kL.png

and have to implement it in Access.

Now I have create the tables with the fields listed in the ERD, made my Primary Keys and also added foreign keys to Match table (RefereeID) and Player table (TeamID) and created relationships for these

http://i.imgur.com/jxLaQ.png

I am not sure how to create the relationship between Team and Match. If I create one foreign key in Match (TeamID) then the details of each match would have to be entered twice, which I can't have.

What can I do?

Thanks

 

[MajP]

homeTeamID_fk (foreign key to home team)
awayTeamID_fk (foreign key to away team)

 

[PHV]

For a many to many relationship the usual way is to build a junction table (aka bridge).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ballabriggs]

MajP: that's what I figured, would it be like so

http://i.imgur.com/k5ZfY.png

?

PHV: While it is a many-to-many relationship, I don't know if a junction table would work? I do find them a little confusing though so you might be on the money with that one?

 

[Ballabriggs]

Sorry for the double post, but I suppose the junction table could be implemented with 3 fields: a primary key for the table along with matchID and teamID, foreign keys to the respective tables?

Would this be better than the team1/team2 solution?

 

[MajP]

You could use a junction table, but in my opinion that would be overly complicated. It is a many to 2. A junction would look like

jncTblMatch_Team
matchID_fk
teamID_fk
teamType(home or away)

Building a user interface for that is more complicated.

so if team A and B are in match 1

1 A Away
1 B Home

A purist may say that is more normal, but not worth the burden in my opinion.

If you did it with three keys that is identical to my solution and gives you no difference.

 

[PHV]

Yep, sorry, a match concerns 2 and only 2 teams, so the junction table is superflous.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ballabriggs]

I went for the HomeTeam/AwayTeam option, but when creating the relationships it ended up like this

http://i.imgur.com/6FW98.png

Access made that Team_1 table by itself, although it does not appear in my list of tables. It is just another instance of the Team table?

Or should it be looking like this

http://i.imgur.com/R1f9v.png

?

 

[MajP]

Instance is probably not the correct word since nothing is "created", but it is an alias for the team table and it is proper. I am pretty sure the second design would be bad, because you would only be able to create matches where you play yourself. Both foreign keys would have to match the same PK. (not certain but you can verify)