469,612 Members | 1,612 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

Many many to many headscratchers

34 32bit
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.
2 Weeks Ago #1
5 7195
32,200 Expert Mod 16PB
Hi again.

Let me start by answering the question straightforwardly - 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 ;-)
2 Weeks Ago #2
224 Expert 128KB
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.

2 Weeks Ago #3
32,200 Expert Mod 16PB
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.
2 Weeks Ago #4
224 Expert 128KB
Hi NeoPa,

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

2 Weeks Ago #5
32,200 Expert Mod 16PB
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!!
1 Week Ago #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
5 posts views Thread by oNLINE bUDDY | 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 devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.