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.
6 16698 NeoPa 32,557
Recognized Expert Moderator MVP
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 ;-)
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 32,557
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.
Hi NeoPa,
Granted! I suppose I didn't read it the same way you did.
Gaz
NeoPa 32,557
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!!
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!
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
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...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
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...
| |