Trouble grasping one to many and many to many. (1 Viewer)

Bettany

New member
Local time
Today, 17:59
Joined
Apr 13, 2020
Messages
26
Hi there, I'm trying to really understand one to many versus many to many, because it's really messing up my database designs I'm pretty sure. To take a common example, courses and instructors. To me, this could BOTH be, one to many and many to many. A course could be taught many instructors (this semester Mr. Jones teaches Sociology 101 and next semester Mr. Smith teaches Sociology 101), making the relationship many to many. But what about two teachers teaching the same course, as in a Professor and a student aide teach the course? Or would the first scenario I mentioned ONLY be one to many, because only one teacher a time is teaching the course, and it doesn't matter that next semester a new teacher could be teaching the course? Thanks :)
 

plog

Banishment Pending
Local time
Today, 16:59
Joined
May 11, 2011
Messages
11,612
To me, this could BOTH be

You are correct, but you must set up your database to handle the worst case--otherwise you have cases that won't fit into your database. A many to many relationship can handle a one to many relationship; a one to many cannot handle a many to many relationhip. Therefore if you want to handle many to many, you set it up for many to many.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:59
Joined
Jan 23, 2006
Messages
15,364
This is a common set up where there are Many Teachers and Many Subjects - Many To Many.
This is resolved with a junction table.
Teachers---->TeacherCanTeachSubject<---Subjects

More detailed info here. And a video here (Dr Soper)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:59
Joined
Jul 9, 2003
Messages
16,245
I have posted about many-to-many on my nifty access website here:-


There's a text explanation and a couple of YouTube videos

The sample database is available for free just use this coupon code:- GetALL4Fr33_OrBuyMeA_Coffee
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 28, 2001
Messages
27,001
One-to-many is a subset of many-to-many. In your teacher example, many students take the class from teacher A. But to make that clearly many to many, I'm sure the teacher would teach that subject again in the same year but a different time. So many students take the class from teacher A in the morning and many more take the class in the evening. So the class roster includes many students and multiple sessions, even for the case of one teacher and once class/subject. One-to-many is the subset when you lock down either a particular time for the sessions OR if you consider that a single student will take many classes in the same day.
 

Bettany

New member
Local time
Today, 17:59
Joined
Apr 13, 2020
Messages
26
This is a common set up where there are Many Teachers and Many Subjects - Many To Many.
This is resolved with a junction table.
Teachers---->TeacherCanTeachSubject<---Subjects

More detailed info here. And a video here (Dr Soper)

That first link was so amazing. Do you know where to find the rest of the chapters?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:59
Joined
Feb 19, 2002
Messages
42,976
A course could be taught many instructors (this semester Mr. Jones teaches Sociology 101 and next semester Mr. Smith teaches Sociology 101), making the relationship many to many
This isn't a M-M relationship. It is a 1-m since each user is teaching the course alone. If a course has multiple teachers simultaneously, that would be a M-M.
 

Bettany

New member
Local time
Today, 17:59
Joined
Apr 13, 2020
Messages
26
Ok from your reading all of your wonderful links, I see what I was getting confused between say "sales" and "customers." And entities and entity types.

How you phrase the question matters - A LOT. For example in trying to establish the cardinality (new word I learned, thank you very much!) between sales and customers, you could very easily select the wrong relationship type by asking the question in an inaccurate way:

Correct: 1 sale is always made to one customer at a time. (leading to a one to many conclusion)
Incorrect: sales can be transacted by many different customers. (sounds reasonable, but leads to an incorrect "many to many" conclusion)

Thank you all so much!!!
 

Bettany

New member
Local time
Today, 17:59
Joined
Apr 13, 2020
Messages
26
As I think about things, I guess I'm a little confused by something else. In looking at a relationship between customers and sales, when do I conclude :"one customer can transact one sale at a time" vs. "many customers can transact many sales." The main difference here seems to be time period, one transaction at a point in time vs. total potential transactions over time. Both considerations seem relevant, but they lead to different answers -- and different relationships. Hmm..
 

Bettany

New member
Local time
Today, 17:59
Joined
Apr 13, 2020
Messages
26
Anddddddd, in reading on in the links, I answered my own question, you have to go with the scenario that has the biggest values for the left and right of what you're considering. So "many customers can transact many sales" is the right way to set up the relationship.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 28, 2001
Messages
27,001
Earlier, I mentioned this and it bears repeating. Having a one-to-many relationship is a subset of having a many-to-many relationship. The deciding element is as a matter of timing. Many customers can buy many products. But at a given moment a given customer can transact one sale (of many products) at a time. TIME is frequently the delineator that splits out a subset from the whole set. It becomes an issue when you try to build reports or analyze trends over time, because a SALES TRANSACTION is an instantaneous thing but a repeat customer lasts for a long time (you hope).

In general, whenever you need to aggregate things (take SUM or COUNT or AVG or other SQL aggregates), you emphasize the "MANY" side of something even though you have the details needed to let you see the "ONE" side. So "When do you conclude something about 1/many vs. many/many?" It depends on whether you are trying to see the forest or see the trees.
 

Users who are viewing this thread

Top Bottom