Understanding One-to-One Relationships

[dzeglen]

Hello,

I am managing a large database of contacts, and some have personal address information, multiple phone numbers, and multiple emails. I thought it would be best to put all this personal info into a separate table with multiple field names for email and phone (email1, email2, phone1, phone2) and create a one-to-one relationship. However, I've heard that one-to-one relationships can be troublesome. Is this a good idea, or is there something else I should do instead?

 

[Addyman]

This is not the best idea and not the best data structure. What happens if you need Phone3 or email3 etc.? You need to keep adding fields to your table.

Have one table for your Contact (Forename, Surname.....) and have an ID field (an autonumber field would suffice) called ContactID.

Then, for any information which may require multiple values, have a table for them. So, you could have a table for email addresses which will have EmailID (numeric) and EmailAddress as fields.

Then, you can use a join table which will have ContactID and EmailID as the two fields.

So each ContactID can be related to one or more EmailID's.

 

[dzeglen]

Hi,

Thanks for your response. I see what you're saying about creating a many to many relationship using a junction table so that each contactID can be related to one or more emailIDs.

I do have a problem though, and its that my contactID is already loaded with two many to many relationships on each side. I've attached what my relationships look like so far so that you can see what I mean.

How could I also add a third many to many to contactID to do what you suggested?

 

[jdraw]

I suggest you read the attached article to refresh some relational database design concepts.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

 

[dzeglen]

Hi,

Thanks for the link. I can see that it's not necessary to create a separate table for some things, like categories or prefixes, correct? Also, I should integrate the personal information into my main contact table.

However, I still require having many-to-many on both sides of my main contact table, for events, and for organizations, as people can attend multiple events, and be a part of multiple organizations. So how I could I account for multiple emails/phones without resorting to having all these empty spaces in my main table?

 

[jdraw]

You can have Contacts -->> ContactPhones as a 1 to Many
You could have a PreferedPhone in the Contacts
You could have HomePhone, CellPhone... if that makes sense in your context.
Same with emails.

To resolve many to Many use Junction Table
see https://www.youtube.com/watch?v=7XstSSyG8fw