471,862 Members | 1,737 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,862 software developers and data experts.

Too many, many to many?

43 32bit
I have slowly and painfully been working on creating an Access database for sports cards.

Database Needs:
For the purpose of this post, a user (me) will need to be able to search for cards by Sport, Location, or Team Name (or a combination thereof).

Could someone kindly look at my screenshot to see if I am overthinking my table relationships? Everything is so connected with many to many relationships and I guess I just was not expecting that occurrence.

One Location can have Many teams
e.g. LA Rams, LA Dodgers, LA Lakers (NFL, MLB, NBA)
or LA Lakers, LA Clippers (all NBA)

One team can have played in many locations
e.g. Boston Braves, Milwaukee Braves, Atlanta Braves (all MLB)

One sport can have many teams
e.g. Braves, Yankees, Dodgers (all MLB)

One team (Name) can be in many sports
e.g. New York Giants (MLB), New York Giants (NFL)

One Sport can be in many locations
e.g. NFL, NBA, MLB all have sports teams all over the place

One Location can have many sports
e.g. Atlanta Hawks (NBA), Atlanta Braves (MLB), Atlanta Falcons (NFL)

MLB = Major League Baseball
NFL = National Football League (American Football)
NBA = National Basketball Association

Any help would be appreciated.
Nov 15 '21 #1

✓ answered by NeoPa

That diagram shows the appropriate way as far as I can see :-)

5 8281
32,462 Expert Mod 16PB
Interesting question. Unfortunately minus any graphic of your current structure.

As far as I can see you probably would need linking tables for all of those connections. That's three such tables to link :
  1. Teams to Locations.
  2. Teams to Sports.
  3. Locations to Sports.
Each link works both ways of course so that saves another three ;-)
Nov 15 '21 #2
43 32bit
I could not find how to attach an image until now. Thanks again for all your help!
Attached Images
File Type: jpg SportsTeams many to many JNC examples.jpg (59.3 KB, 25 views)
Nov 15 '21 #3
32,462 Expert Mod 16PB
That diagram shows the appropriate way as far as I can see :-)
Nov 15 '21 #4
43 32bit
Thank you NeoPa. Also, thanks kindly for your support when I have asked questions in the past.

My next Q for another thread may be how I can incorporate current franchises. For example many of these historic teams belong to one franchise history and I need to be able to search for both current franchise or specific historic teams.

Thanks again!
Nov 15 '21 #5
32,462 Expert Mod 16PB
I'm always happy to help :-)

I'll look out for your next question when you post it. Humorous titles are always good ;-)
Nov 15 '21 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Mikey | last post: by
1 post views Thread by Powell | last post: by
1 post views Thread by Johnny Meredith | last post: by
1 post views Thread by Phil W | last post: by
13 posts views Thread by the other john | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.