dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Creating a Contacts Database in MS Access

How to structure Microsoft Access tables for an extensible contacts management database.

Last updated on 2018-08-22 by David Wallis.


Preface

The majority of databases I’ve worked on over the decades have held the same information about contacts: names and addresses; telephone and mobile numbers; email addresses.

There are templates for DBs (databases) that come with your purchase of MSA (Microsoft Access) and that are available off the web. Of those I have seen, I considered none was fit-for-purpose.

If you construct a contacts database properly from the outset, then extending it, or incorporating it into any future database you plan, will save you a lot of time and possibly a lot of avoidable expense.

Hence this piece. I hope you find it useful in considering your own design and that it helps you to avoid some of the pitfalls caused by poor design and implementation.


Initial Considerations

Linked tables

Before launching into its construction it’s always alot time for considering what purpose your DB is to serve and, were it to prove a triumph, what development it might benefit from in the future.

You will also need to assess your skills levels in respect to how much you know about working with MSA, and to assess what time you can spend on the development of your DB.

Each person who has asked me for help admit that when they created their DB they did not give enough thought to its foundations — to the data tables and the relationships between them, in the process stage of database design oftened called “data modelling”.

Instead, they whacked in a few tables and then spent what they now recognise was an inordinate amount of time on user interfaces and reports. Breakdowns during use lead to subsequent fiddling that wasted more time; and was always going to fail because no amount of fiddling would get around faults in the foundations.


Planning for Contacts

Contacting

In my view there are two sorts of contacts: people and organisations. The information a DB holds for a person will be different from that for an organisation.

First names and last names are the minimum information that you need in a contacts DB to identify a person. That said, people change their names: Jacqueline was first known to me as Lyn, who on marrying not only adopted her husband′s family name but also changed Lyn to Jac.

Does your database need to track any person’s name changes? It well might do if in the future you intend to extended it to track orders, for example.

If Mary Greene placed an order for widgets in 2016 and, as Mary Brown (née Greene), has just placed another one, then your DB needs to identify Mary as the same contact for both these orders.

An organisation is recognised by its business name. That is the minimum you need to record about it as a contact.

The different nature of contact information for people and organisations leads to the conclusion that we need two data tables — one for each type of contact. We resist the temptation to cram everything into one table.

Additionally we must also allow for people to be members — employees, representatives, etc — of organisations.

You need to be certain your planning is complete before moving on to blueprinting your contacts tables.


Planning for Addresses

A person has a home address. She may have a work address, which is different to her home address. She may have an address to which she is temporarily seconded.

An organisation has a head-office address. It may have regional offices, each with their own address.

People and organisations change addresses.

It′s essential to have clear in your mind exactly what aspects of addresses your DB is to cater for before blueprinting your data tables.


Off-the-Shelf Contacts Tables

A number of websites giving guidance on creating contacts databases, present a design for a contacts table similar to this one:

Sample contacts tableIn this table the ContactRef field holds the unique identifier of a person in the DB. Other tables will link to a particular person by reference to her ContactRef.

Imagine that in 2016 you input Mary Greene as a new contact. The DB assigned her record the ContactRef of 695. Other tables will be using 695 to identify Mary Greene.

In 2017 Mary marries and chooses to adopt her partner’s family name of Brown. So what do you do? Replace the "Mary Greene" FullName with "Mary Brown" (losing any trace of a Mary Greene)?

Or, start a new record for Mary Brown, resulting in two contacts in your DB — 695 and latterly 2031 — who in real life are the same person?

Faced with such choices as these is reason enough for me to reject this table design as unsatisfactory. There are a number of other reasons too — see Addresses Table, for example.

Unfortunately, the above table design is widely promoted. When I last checked my Office 365, the supplied template for a Contacts DB came with the table on the left and for a Customer Service DB with the one on the right:

Sample contacts tables

If you are concerned only with bunging something down and are not minded to consider the future, then I say bung it into Excel. Avoid Access.

If you are looking to your business in the future, please get your database off on the right foot. For if you don’t, perhaps I shall be welcoming your call for help in a couple of years time!


Blueprints for Contact Tables

I recommend two tables for your contacts, one for organisations and another for people.

Sample people tabletblOrganisation holds the information about an organisation that does not change with time or circumstances. Its Name is all you need to begin using the table.

Other fixed information, eg VAT Registration Number and Business Registration Number, would go in this table, if needed.

tblPerson holds the information about a person as we know them at the moment, that is, by their Firstname and Lastname. So why the prefix Current?

Because we are building in provision for tracking name changes should our DB need to provide for this in future.

Fixed information about an individual, eg date of birth and NI number, would get fields in tblPerson if needed.


Organisations and Their People

Some, if not all, of the people in your DB might be employed. If you want to keep track of a person’s employment, then we need to plan for this.

An organisation may have many employees; an employee may move between jobs within his employer’s organisation; a person may move between employers.

To record associations of organisations and people, I propose a new table. Name it tblOrganisationPerson.

tblOrganisationPerson has to manage the many-to-many relationships that can exists between organisations and people — an organisation may have many people; a person may belong to many organisations during her working life. Here goes:

Oraganisation people table

This new tblOrganisationPerson provides for an organisation having as many people as you need to assign to it. The table also allows you to track an individual′s employment history across as many appointments as any human could possibly entertain.

The function of the DateFrom and DateTo fields is to allow your database to track job appointments from when each one starts to when it finishes.

In a record in which you have not input a date into DateTo means that the person is still in that job. DateTo is a much more effective means of tracking current job positions than the “IsCurrent” or “IsLive” fields behind check boxes and combo boxes you see in so many DBs.


Blueprint for Addresses

Within a well-designed DB the details of an address need be input once only, into a data table like this one:

Address table

Throughout the database an individual address will be identified by its AddressID.

Only an appropriate ID needs to be passed in order to establish a person’s, or an organisation’s, address. This way repeated typing of all the lines of the same address is avoided.

Contrast this approach with that of the off-the-shelf tables mentioned above. In those, if you had four contacts at the same business, for example, you would need to input the same address four times.


Addresses for People

For your database to hold information about a person’s change of addresses, I propose tblPersonAddress and linking it to both tblPerson and tblAddress:

Addresses table

Leaving tblPersonAddress.DateTo blank is the way that you indicate that the address is the person’s current one.

Keeping track of a person’s address at work is explained in the People’s Work Addresses.


Addresses for Organisations

Perhaps not as often as people, organisations change addresses nevertheless. So a table similar to the one linking people to their addresses provides for the history:

Database tables

Leaving tblOrganisationAddress.DateTo blank is the way in which you indicate that address is the organisation’s current one.


People’s Work Addresses

Provided you have linked a person to an organisation via tblOrganisationPerson and linked that organisation to an address, via tblOrganisationAddress, there is nothing more you need to do to establish the address at which that person works.

The absence of a date in tblOrganisationPerson.DateTo indicates that a person is still with the organisation.


The Six Tables So Far

With your six tables you have the basis for a contacts DB that allows for expansion in the future and for importing into any DB in which you need to record contact information.

These tables provide for:

Your contacts DB can supply Microsoft Word with names and addresses for letters, mail shots, news letters and so on.


Ringing Up

Mobile phone

We should take these factors into account when planning for phone, mobile and fax numbers:

Let me know if you need tables for phone numbers urgently and I will get on with them.


ContributionIf you find Tips useful and would like to make a donation to encourage me to keep adding to them, please click Donate and let me know how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


Email Addresses

We should take these factors into account when planning for email addresses:

Those requirements lead us to a table that will provide a one-to-many relationship between a person and her emails:

Person email table

If we want to record to what purpose each address may be put, we could add the tluEmailPurpose table:

Email Purpose table

I apply the prefix tlu to the names of tables serving the same purpose as this one — delivering predetermined lists. tlu distinguishes such tables from tbl-type ones, which carry variable data.

Combo box Entries in tluEmailPurpose provide the user with a list of the purposes that we decide upon. The list can then be the record source of any combo box we want to include in a form.

At any time you choose, you can add to the list. With this flexibility built-in, your DB does not need structural modification when you come to need “Brochure” added to the list.


Download Data Model

The download is a zip file DMW_Contacts_Data_Model.zip. Extract to get the file DMW_Contacts_Data_Model.accdb that contains the tables discussed on this page.

DownloadIf you intend using this model professionaly or commercially in any of your databases, then please consider making a contribution.


Shorcomings of This Data Model!

We have our tables and relationships for our contacts database. Now is the time to reflect: have we fallen into the traps alluded to in Initial Considerations; does the data model suit our particular type of business; is it set up to accommodate modules that would expand versatility?

Critically examining the data model we’ve pursued, you may have spotted the shortcoming that I write about on the Party data model page.

“See first that the design is wise and just:
that ascertained, pursue it resolutely;
do not for one repulse forego
the purpose that you resolved to effect.”

William Shakespeare, Romeo and Juliet