473,685 Members | 3,005 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Many many to many headscratchers

43 New Member
I am building a sports card database (or trying to). (In MS Access)

The sports card database necessarily will require a lot of many to many table relationships.

Me. I am have trouble grasping some instances of the many to many concept.

Example1: Student to Classes (I understand this)
One student can have many classes
One class can have many students

Example2: Cities to States
Key = City, State
Washington, GA
Washington, IL
Washington, IN
Washington, IA
Washington, OH

One State can have many cities (OK no problem here)
One city can be in multiple states (well....?)

It's clear that one city name can exist in multiple states
But is the many to many question we are fulfilling in database design asking if one city is LITERALLY in many states, or is one city's NAME in many states?

I am trying to understand this before moving forward further and would appreciate any advice. Because I am not naturally good at grasping database concepts I tend to miss things many times before understanding and also often overthink things.
Nov 16 '21 #1
6 16705
32,569 Recognized Expert Moderator MVP
Hi again.

Let me start by answering the question straightforward ly - It's about which state the city's in rather than which states contain cities with the same name.

Let's look at the logic of it. What are you wanting the database to manage? Is it cities? Or is it city names?

If it actually were the latter then that would change the answer. It isn't though. From your description - and the fact that names of cities aren't really anything you'd be likely to care about too much if managing sports cards - you're desirous of managing what fits where overall, and that includes by city.

That's actually a very necessary technique when working with database design. "What do I actually want it to do for me?". Asking that question of yourself will often answer your questions without the need to involve others.

Should you need to though, we're here of course ;-)
Nov 16 '21 #2
228 Recognized Expert New Member
Hi all,

I'd like to pipe in to this and any future discussions with my experience dealing with items that must be connected loosely to multiple other items.

I have tackled this problem many times before by using key pair tables.


T1ID, Interesting columns....

T2ID, Interesting columns....

[Key], T1ID, T2ID

An example I can give is tagging products to appear in multiple catalogues.

The key thing to consider here is that catalogues don't need make sense from inside the perspective of the product, nor do they have any impact on the relationships the products and related objects have, but when end users access a catalogue the key pair table will be queried to retrieve all products and associated objects tagged with its ProductID.

This normally presents in the UI as an extra tab where users tick the relevant items.

In direct response to the OP, the cities should be unique, regardless if they have the same name.

Conceptionally, you know in your mind that Washington, GA is not the same as Washington, IL so they should be defined that way digitally.

Nov 17 '21 #3
32,569 Recognized Expert Moderator MVP
Hi Gaz.

Can you explain what you would see as the difference between a {Key Pair] table and a Many-to-Many linking table. It looks exactly like what the OP was already talking about to me.

I obviously can have no argument with the rest of your post as it agrees with my own comments perfectly.
Nov 18 '21 #4
228 Recognized Expert New Member
Hi NeoPa,

Granted! I suppose I didn't read it the same way you did.

Nov 18 '21 #5
32,569 Recognized Expert Moderator MVP
I guess having been here for so long and read so many different things, in even more different ways, I know I have to read very carefully and I am also able to recognise what people are saying sometimes - even when they say it differently from how I might.

On the plus side of your earlier post - it was bang on accurate!!
Nov 18 '21 #6
43 New Member
This helped me.

"Conceptionally , you know in your mind that Washington, GA is not the same as Washington, IL so they should be defined that way digitally."

Thank you all!
Jan 5 '22 #7

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

Similar topics

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 the O'Reilly "Managing and Using SQL" (I think that's it). Anyway, I was looking at a chapter discussing database design (without any direct reference to impementations in MySQL), and they mention Obj/Relationaldesign as a way to have a...
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 if the DDL for the tables and the SQL for the view.
by: oNLINE bUDDY | last post by:
How can you reverse a many-to-many XML structure between 2 tags? Lets say we have a books/author XML file. A book can have many authors. <book1> <Author1> </Author1> </book1>
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 how many relationships to create. i am also trying to figure out what relationships to create.
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 must list each department manager that reports to the recipient. For example: John Doe - Director - Department 10 (recipient of the email) Reporting Managers: Jane Doe - Manager - Department 10A
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 various ways to the books (e.g. editors, illustrators etc), so there's more than one many to many relationship there. When there's an entry in all the tables relating to the book I want to view, everything works fine (i.e. there's an author in...
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
by: radiodave | last post by:
I don't know how to ask this question but I'll try: I'm making a database (Access 2003) for an Opera buff. I have tables related like: each Opera has many Productions (Madame Butterfly has an SF Opera production) each Opera has many Roles (Madame Butterfly has Cio-Cio-San) each Role has many Persons (Cio-Cio-San is played by Patricia Racette) But the Person playing the Role depends on the Production. So, just like the program you get...
Jerry Maiapu
by: Jerry Maiapu | last post by:
This is simple question but I know if solved could greatly assist many data entry clerk today, in the way they do data entry. I have about 13 tables and are married to each other with one to many relationships. Ok about 12 of them are basically tables containing certain list of values. So basically I have a circular type relationship where there a table1 in the middle with the rest connected to table 1 having a one to many relationship....
by: cmo187265 | last post by:
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...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.