Creating a Contacts Database
How to structure Microsoft Access tables for an extensible
contacts management database
Last updated on 2018-01-15 by David Wallis
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 DB (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.
Before launching into its construction it’s best to spend time 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.
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.
Planning for Contacts
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:
In 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:
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.
tblOrganisation 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:
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, this means 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:
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:
Leaving tblPersonAddress.DateTo blank is the way 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:
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:
- Tracking people’s name changes
- Addresses for people and organisations
- Tracking address changes for people and organisations.
Your contacts DB can supply Microsoft Word with names and addresses for letters, mail shots, news letters and so on.
We should take these factors into account when planning for phone, mobile and fax numbers:
- People can have more than one number
- People change their numbers
- A person may have different number for different purposes —
- family and friends, business, etc
- Organisations’ numbers follow those patterns.
Let me know if you need tables for phone numbers urgently and I will get on with them.
We should take these factors into account when planning for email addresses:
- People can have more than one email address
- People change their email address
- Peoples’ email addresses may serve different purposes —
- family and friends, business, etc
- Organisations’ email addresses follow these patterns.
Those requirements lead us to a table that will provide a one-to-many relationship between a person and her emails:
If we want to record to what purpose each address may be put, we could add the tluEmailPurpose 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.
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.