dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Party Data Model for Access Databases

Understanding the party data entity’s part in a RDBMS data model and the tables to use in a Microsoft Access database.

Last updated on 2018-09-12 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?


People, 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


ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


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. (Last year I was 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.)


Test Your Data Model

At first glance you may consider that the above people/organisation/role model works for your business. But does it? Before adopting it, I encourage you to test it out with scenarios befitting your business.

I don’t know your business, so let’s say it’s called Complex Org. Complex Org has two divisions: Complex North and Complex South. These divisions have departments, but not necessarily the same departements. For instance both have an Accounts department, but only Complex North has a Photography and a Print department.

Natalie is an employee of Complex Org working at Complex North in Photography. Her role is in Post Production. Do you reckon this is a reasonable test for the model? If so, will it accommodate Natalie, her role, her position within the company and the divisional structure of the company? That’s your Starter for 10.


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.


Download Data Model

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

DownloadIf you intend using this model professionally or commercially in any of your databases, then please consider making a contribution towards the time I’ve taken making it available to you. Thank you.

“Design should not dominate things, should not dominate people. It should help people. That’s its role.”

Dieter Rams, industrial designer