Data Modelling the Relationships Between People and Organisations

How, in an Access database, to model the relationships between people and organisations and the roles of the parties within those relationships.

Last updated on 2024-05-18.

Introduction

If you haven't given any consideration to data modelling as a process, then reading Data modelling in Practice might be a useful primer before getting stuck in to peoples and organisations.

In How to Apply the Party Data Model to Access Contacts Databases, I propose a data model in which a person can be associated with one or more organisations. That model is adequate if all you seek to record is for which company a person works.

But what if you want to track associations between companies? Track to which companies Company A is a supplier, say?

Or if you what to record associations between people? Track who is Donald Duckworth's next of kin?

In this article you'll find a data model for such relationships in an Access database. My aim is to put the tracking of relationships in the hands of the database users with no need for them to call on the database's developer.

Parties, Relationships and Roles

The party entity efficiently manages the recording of information that may apply to both people and organisations, e.g. postal and email addresses.

The tables for parties, people and organisations — tblParty, tblPerson and tblOrganisation — are described on the How to Apply the Party Data Model to Access Contacts Databases page.

In this article, we make use of those same three tables but extend the data model to accommodate inter-party relationships.

What springs to the database developer's mind on hearing the term relationship is the relational linking of data tables, the foundations of a relational database management system (RDBMS). But in this article, relationship is used as it is in ordinary conversation — the relationship between two people, for example.

In order to establish the nature of a relationship, I make use of a relationship type entity and a role entity.

In the screen shot, the two organisational relationship-type records establish that Bauch-Koss (Group) acts in its role as the parent company of Bauch-Koss (UK), its subsidiary; and that Bauch-Koss (UK) supplies — in its supplier role — to Abernathy-Kulman, the customer.

My way of catering for a relationship is to consider it fitting into one of these groupings:

• Organisation to Organisation Organisations are corporations, governmental entities, partnerships, limited companies, cooperatives, divisions of businesses, departments, charities, clubs, families and so on.
• Person to Organisation I get asked why not Organisation to Person. Well, my instinct is that on most occasions when dealing with an organisation it's an individual in that organisation with whom I try to make contact. So, Person to Organisation fits for me.

• Person to Person A person is an individual identified by, at least, their first name and last name.

The data model I'm presenting here puts no restrictions on the type of relationship type or on the roles. You choose these to suit your database, as explained below.

The Tables

In this diagram of the tables used to record relationships involving people and organisation, it's a record in tblPartyRelationship that establishes a particular relationship between two parties and the role each party plays in that relationship.

tluPartyRelationshipType and tluPartRole store the lists that appear in combo boxes, as explained in the next section./p>

Maintaining the Lists

This is a screen shot of the List Maintenance form used by the database user to manage the relationships and roles:

The lists populate the combo boxes in the Party Relationships form:

Reporting the Relationships

This is the result of a query that summarises the relationships between the various parties:

In this raw form the query provides the user with plenty of filtering and sorting possibilities.

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