473,231 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Too many, many to many?

43 32bit
Background:
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).

Problem/confusion:
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)

Key:
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 8377
NeoPa
32,554 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
cmo187265
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, 32 views)
Nov 15 '21 #3
NeoPa
32,554 Expert Mod 16PB
That diagram shows the appropriate way as far as I can see :-)
Nov 15 '21 #4
cmo187265
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
NeoPa
32,554 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

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

Similar topics

3
by: Mikey | last post by:
Hi all. In the process of trying to figure this thing out, I've been doing the old "stand around in the store and read as much as possible before you look like a derelict" thing. This time, with...
2
by: Keith | last post by:
I am having a problem creating a many-to-many-to-many type relationship. It works fine, but when I create a view to query it and test it, it does not generate the results I expected. Below...
1
by: ning | last post by:
It's easy to present "One - Many" relationship in XML, but how to present "Many - Many" relationship in XML?
1
by: Powell | last post by:
I have a requirement to return some information from a large number of xml files I have tried XSLT, which quickly gets overwhelmed. Found references to XQuery and it seems like it might work I...
2
by: Megan | last post by:
hello everybody, i know this is a very long post, but i wanted to provide as much detail as possible. quick overview- i want to create a couple of many to many relationships and am wondering...
1
by: Johnny Meredith | last post by:
Hi, I'm building a database that, once a year, generates surveys that are emailed to people in our organization. The recipients of the surveys are managers of various departments. The emails...
1
by: Phil W | last post by:
Hello, I have set up my database in access using many to many relationships (it's the good ol' books and authors one again). I've actually extended it to include other people who contribute in...
13
by: the other john | last post by:
The trouble currently with 3 tables. I'm excluding non-relevant fields... tbl_users PK_user_ID tbl_developers PK_developer_ID FK_developer_user_ID FK_developer_project_ID
2
by: The Frog | last post by:
Hello everyone, I am trying to find way of writing an SQL query that can produce missing record combinations across a many to many type setup in Access. The three tables used are as follows:...
0
by: fred.flintstone | last post by:
I get warnings in the windows event viewer every few seconds.. See below. Why is this ? Event Type: Warning Event Source: DB2-0 Event Category: None Event ID: 5 Date: 22/08/2007...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

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.