DMW logo
tel 01732 833085
tonbridge · kent · UK

How to Apply the Party Data Model to Access Contacts Databases

How to data model the tables of a Microsoft® Access contacts database to include the party entity to accommodate the roles of people and organisations.

Last updated on 2020-08-26 by David Wallis.


Preface

On the Contacts Database page I finished by questioning whether the data model proposed was entirely fit for purpose. For businesses where organisations and people have separate and distinct roles, the model may fit well.

But for those businesses in which people and organisations can share the same role, that model is unsatisfactory. On this page I introduce the party entity to sharpen up the model.


People and Organisations

This is the model proposed for that contacts database:

Contacts data model

Each of the entities people and organisation carries its own suite of tables with which to record their details — to record addresses, for instance, the tblPersonAddress and tblOrganisationAddress tables.

To record email addresses, people have tblPersonEmail:

Database email tables

For organisations we would have to add a tblOrganisationEmail table.

tblPersonEmail and tblOrganisationEmail would be identical in structure, varying only in the names we choose for a couple of fields. In making this observation we’re beginning to think there might be a better data model to use than this one.

We can take our suspicions further by considering the roles of people and organisations. In a solicitor’s practice, clients may be people and organisations. For a coffee shop, people and organisations may be both suppliers and customers.

To accommodate the roles requires another table for people and another for organisations, each of the same structure. Yet further proliferation of tables to fuel our suspicion that there might be a superior data model?


Person, Organisation and Party Data Model

What I propose for our revised contacts database is the introduction of a party entity. It embraces both people and organisations, and appears in the database structure as tblParty:

MSA party data model


Party Email Efficiency

By considering email, we can see how the introduction of tblParty rationalises the provision for emails — a single table, tblPartyEmail, catering for all email addresses:

MSA party email


People and Organisation Roles

Following the scheme for email, we can accommodate roles simply by addition of the tblPartyRole and tluRole tables:

MSA party role

Role tabletluRole is the one-stop place for maintaining the list of relevant Roles. It can be the row source for drop-down lists on forms in your user interface.

When you make this table accessible only to your database administrator you ensure that users are unable to introduce spurious roles. (A couple of times I’ve been asked to convert a database in which role was a free-text entry in a person or organisation record. There were hundreds of different roles, including misspellings! Composing a query upon which to base a Suppliers Activity report had proved well nigh impossible.)


Limitations of the Role Model

At first glance you may consider that the above party role model works for your business. But does it? Before adopting it, please test it thoroughly with scenarios befitting your business.

The model described on this page is OK for tracking party roles up to a point. But it’s emphasis is on the role a second party performs in relation to the owner of the database.

For example, if Glam Co Ltd runs the database, the database can identify Nice Smells Ltd as a supplier to Glam Co Ltd. But it can’t log Nice Smells Ltd as a subsidiary of Nice Smells International. And it can’t log Coco Canal as the SEO of Nice Smells International.

To represent any interrealtionships between any parties, the tblPartRole/tluRole area on the model needs revising, as explained in Data Modelling the Relationships Between People and Organisations.


One-to-One Relationships

Amongst the relationships between the tables, you’ll notice two one-to-ones. The need for these is dictated by the party entity’s function, which is to link tblParty to the tables tblPerson and tblOrganisation.

tblParty supplies a unique ID to each record in tblPerson and in tblOrganisation. For the model to work as intended, an ID in tblParty will appear in tblPerson or in tblOrganisation, but never in both.

Considering the record for a new organisation, for example, these are the steps to creating it:

  1. Create a new record in tblParty
  2. Take the ID of this new record
  3. Create a new record in tblOrganisation
  4. Supply tblOrganisation.PartyID with the ID
  5. Complete the information in the organisation record.

In my databases I place a New Organisation button on the organisation form of the user interface. The on-click event of the button is coded to complete those five steps. The user is completely shielded from any activity to do with linking parties and organisations.


Clearing Up Some Confusion

Since publishing this piece on the party data model, I’ve received a number of questions, like this one:

Regarding your party data model for MS Access-Tip, I have been developing a contact database for a while with long-standing concerns about how to handle phone numbers and email addresses. This tip seems to lay out a well thought solution. But I am confused and would like to ask if in tblOrganisation and tblPerson, the field PartyID should be the PK and set to AutoNumber as it is in the downloadable file? ...Or should these tables have a PK field called OrgID and PersonID respectively and both have a FK of PartyID? (The value for which can only appear in one table or the other as a FK)

Thank you very much in advance.

L, Illinois.

These are the tables that are the cause of any confusion and which exist in one-to-one relationships:

Party data model

The crux of the scheme is that tblParty provides a unique ID — the PartyID. Each PartyID generated in tblParty appears in either tblOrganisation or tblPerson but never in both.

Normally, I assign the Autonumber data type to the tblParty.PartyID field, thus leaving Access to generate a new PartyID when needed. .

To satisfy the one-to-one relationships, both fields tblOrganisation.PartyID and tblPerson.PartyID are assigned the Number data type.

I believe what has caused the confusion as exressed by L, above, is that L is referring to the “download” from the How to Create a Contacts Database in Microsoft Access page. That page describles a database, as it appears in the download, that does not make use of the Party entity at all.

The model on this page is fundementally different to the model on the page from which L took his download. I present the two models as alternatives to one another.


Your Support for dmw TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“Good Morning David: Greetings from the USA! I’m an amateur with MS Access. I read your two articles on database modeling: Creating a Contacts Database in MS Access; and Party Data Model for Access Databases; and found them incredibly helpful for my project. My project involves business establishments & business owners. The owners may own one or more establishments.” JW, 2019-01-08.