Am I understanding many to many?

[dniezby]

I'm really just learning much about Access and love it. Some things are easier to understand than others. One of my difficult areas is relations. Basic 1:1 are easy, it's when I have to M:M or Subqueries etc.

Can you please take a look at this situation I'm having and let me know if I'm understanding it correctly and if I should do something else?

I have two tables.

tblContacts and tblEmailAddresses



Originally, I just created a relationship between email addresses and contacts however, I just learned about M:M relationships and was wondering if I should create a Junction table between the two?

In my tblEmailAddresses I only have four fields.

ID (PK)
Contact_ID (FK) to Contacts
EmailType_ID (FK) to email types
EmailAddress

Right now, I have a relation between Contact_ID and ID of tblContacts . Which I understand works wonderfully, if a contact only has one email address. However, if I'm learning correctly, my contacts may have many emails addresses (i.e. Tech Support, Customer Support, Product Support etc) as noted by my other column - EmailType_ID


So....If I understand when I'm doing, I need to create a new table:

tblContacts_PhoneNo

Contact_ID
PhoneNumber_ID

Then obviously link Contact ID with ID from the contacts table and PhoneNumber_ID with the ID from the phone numbers table.


Am I understanding this correctly? Any other recommendations?

[andy49]

Whilst you're very clear that one contact can have many email addresses,

You're not quite clear on whether a single email address can belong to two or more contacts?


Sent from my iPad using Tapatalk

[June7]

Many-to-Many if each contact can have multiple emails AND each email can associate with more than one contact.

[andy49]

This makes it very clear I think....

https://support.office.com/en-gb/art...b-1c0b8c1f5653



Sent from my iPad using Tapatalk

[dniezby]

OOOOooohhh okay, I see....So I would use the M:M only when both sides would associate with multiple records. So I should create one for phone numbers since a husband and a wife may have the same number but they're different contacts right?

[June7]

It is a balancing act between normalization and ease of data entry/output. A lookup table of phone numbers might be just 1 step too far for me.

[CJ_London]

different contacts would have different records, as to whether they have different phone numbers is down to your requirements. You may also have an address table which may or may not have a many to many relationship with contacts - can one contact have many addresses? Either way, suggest a mobile number is usually unique to the contact, whilst a land line is unique to an address. You may also need a field to bind husband a wife together so you know they are related (surname alone is not enough) - this might be a FK in the contacts table so the table 'looks up' itself. Or it might be a many to many table 'relationships' table because you may also have other relatives that live at the same address.

You also need to consider whether you need to maintain a history or whether 'current view' is sufficient - person A moves from address1 to address2 - do you still need to know about address1? if so then you have a many to many relationship between address and contacts.

[dniezby]

Wow. That just blew my brain. I don't think, for my application that I need to get that detailed.


Sent from my iPhone using Tapatalk

[andy49]

But it certainly makes one think. Thanks Alex. Helped me no end.


Sent from my iPhone using Tapatalk

[dniezby]

I probably shouldn't ask this here but since we are talking about the many to many table. Does this change how I put phone number controls on form? Do I use the phone numbers table still?


Sent from my iPhone using Tapatalk

[CJ_London]

if a contact can only have one phone number I would say no - put the phone field in the contact table. As June says in post #6 - it is not worth having a separate table for phone numbers, even if several contacts share the same one