Many many to many headscratchers

[cmo187265]

Background:
I am building a sports card database (or trying to). (In MS Access)

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

Problem:
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.

[NeoPa]

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 ;-)

[GazMathias]

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.

Example:

T1
T1ID, Interesting columns....

T2
T2ID, Interesting columns....

T1_T2
[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.

Gaz

[NeoPa]

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.

[GazMathias]

Hi NeoPa,

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

Gaz

[NeoPa]

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!!