How to Apply the Party Data Model to an Access Contacts Databases

If you haven't given the process of data modelling any consideration, then Data Modelling in Practice might be worth a read before following this article.

Last updated on 2024-05-19.

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 that model is unsatisfactory when people and organisations can share the same role. E.g. when both Dr Sam Priceless and Amazin PLC are customers for your bird box producats.

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:

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:

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:

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:

Before adopting this model, please read the next section.

Limitations of the Party Email Data Model

The limitations of data model for emails, above — tblPartyEmail and tluEmailPurpose — become evident when these questions are put to it:

• What if a party has more than one email address?
• What if an email address can be used for more than one purpose?

Imagine Maritza Welham, one of our contacts, has two email addresses. To one — m.welham@steich-waters.org — we direct newsletters, product updates and events messages.

To the other, maritza10@gmail.com, private messages only, i.e. messages that have nothing to do with business.

In 2012 Maritza switched her private email to maritza10@gmail.com from mw9876@yahoo.com.

Hence, tblPartyEmail will aquire one record of Maritza's m.welham@steich-waters.org address, and two records of Maritza's personal email addesses.

What we're acknowleding here is that there is a one-to-many relationship between Maritz's business address and the purposes accorded to it. Therefore this table arrangement satisfies good practice:

tblPartyEmailPurpose will aquire as many records as prove necessary to assign purposes to each of Maritza's post boxes.

How does the database record which of Maritza's email addresses are current? By leaving blank (Null) DateTo in tblPartyEmailPurpose.

People and Organisation Roles

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

tluRole 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's or organisation's 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 CEO 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 to each record 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:

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

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 their 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) —

Thanks, in anticipation.