DMW logo
tel 01732 833085
tonbridge · kent · UK

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 2019-08-24 by David Wallis.


Introduction

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 one method of representing such relationships in an Access database. My aim is to put the management 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 my 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.

Inter organisation relationships

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:

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.

Relationships query

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:

Relationship types form

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

Relationship types combo
Relationship types combo

Reporting the Relationships

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

Relationship types combo

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


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, keeping it free of advertising.

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 —

Invoice

Thanks, in anticipation.

SSL Cerification

“Assumptions are the termites of relationships.”

Henry Winkler