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 2019-04-15 by David Wallis.
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:
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:
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. (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.
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:
- Create a new record in tblParty
- Take the ID of this new record
- Create a new record in tblOrganisation
- Supply tblOrganisation.PartyID with the ID
- 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.
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 cause the confusion as exressed by L, above, is that L is referring to the “download” from the Create contacts DB page. That page describles a database, as it appears in the download, that does not make use of the Party entity at all. The Party data model of this page is fundementally different to the model on the other page. I present the two models as as alternatives to one another.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.