many to many relationships in ms access

Question

I have a database (for a pet sitting company) containing tables for the following:

• Customers
• emergency contacts
• phone numbers
• phone types

The phone numbers are stored in a separate table to allow for efficient storage of a virtually unlimited number of phone numbers per customer. The phone numbers table stores both the customer ID and the phone type ID in addition to the primary key. My question is - is the best way to allow for emergency contacts to have the same functionality with phone number records to add another field to the phone numbers table "emergency contact ID"? Or should I be storing emergency contacts in the same table as customer (and rename it Individuals)? If so, please tell me how to create a relationship between records in the same table.

Thanks so much, Jessica

Answer 1

You have to ask yourself the question: how far should you really go to respect data normalisation rules?

I'm not sure what the PhoneTypes table would contain, but if it is a list of things like Mobile, Work, Home, iPhone you are probably going a bit too far already: you're not building a contacts application, you're building a Pet Sitting application, there are probably more important areas of the software that would demand your development time.

Increasing the complexity of software is costly: time to mimplement features tends to creep up, as well as complexity and along with it the risks of errors, cost of maintenance, and more often than not, performance suffers as well.

These contacts details are really just properties of the Customer.

A Customer can have multiple telephone numbers and multiple emergency contacts.
Usually these should be listed in order of importance, so if the needs arises, you call the most relevant person first.

Without more information, the way I would handle this would simply to leave 2 memo fields in my Customer table where the user of the app can enter that data in any way she pleases, so she can list it in the right order, make annotations as necessary (Call on Mondays only, Customer's mum, call after 11:00am only, etc).

You can further constrain data input if you wish, like have a textbox where the user enters the details before clicking an 'Add' button that will append the data to the field, for instance by using a semi-conlon or simply CrLf to separate records. The data can then be split on the semi-colon or CrLf and shown in a listbox on the form for better presentation.
You can handle both Customer Phone numbers and Emergency contact numbers in the same way.

This makes things simple: all the Customer data is in one table instead of being split across multiple tables, with no unnecessary joins, it won't take more space than using multiple tables (actually, it will save space). It makes reporting easy (you can simply show the customer list and that will show all available phone numbers for all customer without you having to do anything fancy), it makes searching easy as well.

Having multiple values in a single field is quite common for peripheral data.
Unless you absolutely need to separate contacts, and make complex reports based on them or make sure you can re-use them, you do not have to create tables for every bit of information. Let the application user enter what is relevant for the Customer.
Constrain data entry to format it and check its consistency if you want, but ultimately, unless the purpose of the software is to maintain a complex contact list, don't make it harder than it probably should. A bit of VBA and some string manipulation is sufficient to constrain the data, allow it to be rearanged in the order that's most relevant by the user, and it will make your app snappier by avoiding some complexity.

Anyway, I would start with something simple anyway and see later if splitting the data accross multiple tables makes sense.
Avoid premature optimisation.

However, if you feel you really need to handle this by the book, I would probably handle it as follow:

Store everything in a Contact table that could have properties like these:

• ID: unique contact ID
• PhoneNumber: TEXT
• PhoneTypeID: (whatever that is if it links to your PhoneType table)
• IsEmergencyContact: BOOLEAN
• ContactName: TEXT, freeform, how to address the contact person
• CustomerID: foreign key linking to the Customer table
• Notes: MEMO, any useful info about the contact
• Rank: INTEGER, a sortable rank of importance for this contact

If you want to decouple the Customer from the Contact, so you can re-use the contact for multiple customers, you would need an intermediary table:

The Contact table would become:

• ID: unique contact ID
• PhoneNumber: TEXT
• PhoneTypeID: (whatever that is if it links to your PhoneType table)
• ContactName: TEXT, freeform, how to address the contact person
• Notes: MEMO, any useful info about the contact

And the CustomerContact table (that makes the many-to-many relationship possible):

• CustomerID: foreign key linking to the Customer table
• ContactID: foreign key linking to The Contact table
• IsEmergencyContact: BOOLEAN
• Rank: INTEGER, a sortable rank of importance for this contact

To display and manage the list of Contacts and list of Emergency contacts, you simply need to filter each listbox or subform where you show the information based on whether IsEmergency is true or false.

Now, if you want the same contact to have multiple phone numbers, you will have to split everything even further:

The Contact table would become:

• ID: unique contact ID
• ContactName: TEXT, freeform, how to address the contact person
• Notes: MEMO, any useful info about the contact

A PhoneNumber table would contain:

• ID: Phone record ID
• ContactID: foreign key linking to The Contact table
• PhoneNumber: TEXT
• PhoneTypeID: (whatever that is if it links to your PhoneType table)
• Notes: MEMO, any useful info about this particular phone number

Now you have 4 tables to store all the info you need and share it any way you want, so a Customer can have multiple contacts (emergency or not), Contacts can have multiple phone numbers, Contacts can be shared accross customers (so one customer's contact is another customer's emergency contact):

• Customer
• Contact
• PhoneNumber
• CustomerContact

As I said, doing it the right way will entail a lot more complexity than maybe you really need.

Be careful about not building complexity prematurely. It's nice to anticipate the worse case scenario, but often, it means that you are prematurely optimising and therefore spending time on an area of the software that is not as important as the core of your app.
You always have to ask yourself: should I spend 2 days implementing this or spend 2 days refining the UI, testing or adding code to ensure data integrity, etc?

More often than not, YAGNI

Answer 2

I'd store it the same way as you store phone numbers; in its own table. This allows you the ability to store multiple numbers, and some people may have multiple emergency contact numbers. You always want to think about scalability when designing a database, and plan for the most complex situations. For example, I would imagine with pet sitting that a lot of your customers would come through word-of-mouth, and it's very possible you'll use the same contact for multiple clients.

Answer 3

Your first instinct (storing customers and contacts in a single table) was correct. If you think about it, customers and contacts are both people. It's just that both customers and emergency contacts are a specialised case of people. We can model this using a relational DB.

Let's create a table to hold info about people:

create table tblPeople (
  ID autoincrement primary key
, FirstName varchar(100)
, LastName varchar(100)
, Notes memo
)

Now let's have a table to hold info about customers, but enforcing the fact that customers must also be people:

create table tblCustomers (
  ID long primary key
  constraint Customers_ID
  references tblPeople (ID)
, EmergencyContactID long
  constraint Customers_EmergencyContactID
  references tblPeople (ID)
)

This is called a one-to-one relationship and is used to implement specialisation--like inheritance in object-oriented programming.

You have a choice here. Do you want to let each person have an arbitrary number of phone numbers of arbitrary types? This is obviously more general and more powerful. But also more complicated. Or do you want to go back and just store a fixed number of phone numbers for each person?

Let's say you want to do the former just to take it all the way. In that case, you need a table to hold phone numbers:

create table tblPhoneNumbers (
  ID autoincrement primary key
, PhoneNumber varchar(15)
)

Notice how we don't specify here anything about what type of phone number it is. That part is next:

create table tblPhoneNumberTypes (
  ID autoincrement primary key
, PhoneNumberType varchar(20) not null
)

Now we associate each person with a phone number and type:

create table tblPeople_to_PhoneNumberTypes_to_PhoneNumbers (
  PersonID long not null
  references tblPeople (ID)
, PhoneNumberTypeID long not null
  references tblPhoneNumberTypes (ID)
, PhoneNumberID long not null
  references tblPhoneNumbers (ID)
, constraint People_to_PhoneNumberTypes_to_PhoneNumbers_PK
  primary key (
    PersonID
  , PhoneNumberTypeID
  , PhoneNumberID
  )
)

Here each person (and therefore each customer and each emergency contact) can have an arbitrary number of phone numbers of arbitrary types. Hence this is actually a many-to-many-to-many link table. I believe that is the key (or let's say 'secret sauce') to your contact-phone type-phone number model.

In link tables like the above I prefer to use multiple-column primary keys as I feel there is no useful purpose to an integer primary key column. Here the primary key enforces the fact that each person-and-phone-number combination should be listed only once, with one phone number type.

Note that the above is all valid Access ANSI-92 SQL.