INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need advice implementing this database

Need advice implementing this database

(OP)
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

RE: Need advice implementing this database

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

RE: Need advice implementing this database

(OP)
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?

RE: Need advice implementing this database

(OP)
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?

RE: Need advice implementing this database

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.

RE: Need advice implementing this database

(OP)
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 ?

RE: Need advice implementing this database

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close