Need advice implementing this database

Ballabriggs · Mar 28, 2012

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 · Mar 28, 2012

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

PHV · Mar 28, 2012

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 · Mar 28, 2012

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 · Mar 28, 2012

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 · Mar 28, 2012

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 · Mar 28, 2012

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 · Mar 28, 2012

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 · Mar 28, 2012

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)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need advice implementing this database

Ballabriggs

MIS

MajP

Technical User

PHV

MIS

Ballabriggs

MIS

Ballabriggs

MIS

MajP

Technical User

PHV

MIS

Ballabriggs

MIS

MajP

Technical User

Similar threads

Part and Inventory Search

Sponsor