Redgate Data Modeler

14 July 2016

Party Relationship Pattern. How to Model Relationships

Relationships are everywhere: between people, between organizations, between organizations and people. Think about being an employee of a company, being a member of a project team, or being a subsidiary of another company. Is there a straightforward way to accurately model and manage all these relationships? Can we easily answer the question ‘Who knows who?’

A Quick Review of Relationships

Exactly how this basic model was derived was described in my previous article, Flexible and Manageable Bill of Materials (BOM) Designs.

In this model and in conventional BOM design, the 1st interactor tends to be the superior Party in the Relationship – employer rather than employee, team leader rather than team member, etc.

Here’s what the data might look like (with the role each party plays in parentheses):

A More Sophisticated Model

Imagine that you want to model a project development team like the following:

Source: https://www.edrawsoft.com/template-matrix-org-chart.php

Most of the roles in this team hierarchy are real – e.g. the requirement analyst reports to the system analyst. Another way of looking at it is that the system analyst manages the requirement analyst.

Relationships between roles can be read from left to right (LTR) or from right to left (RTL). It’s normally best to stick to one convention or the other – LTR or RTL – but in practice you may find that there are exceptions to this.

Also, notice that this diagram shows different ways of grouping roles. Some roles are real, as we’ve discussed; others are are logical – e.g. the technical group, the training group, the core team, and the support team.

We can say that this diagram defines team structure using the roles required to complete the project development team. This is distinct from an actual instance of the team, which would be made up of real people’s names against each of the roles.

So we need a data model that is flexible and configurable, such as this one:

The yellow tables contain metadata, and the blue tables contain business data.

Setting Foundation Metadata

We’ll start by populating the party_type table. This table differentiates whether a party is a person or an organization.

Before we do much else, we also need to define roles in the role_type metadata table:

You’ve no doubt noted that each role belongs to either a person or an organization. To give an idea of what is possible, I have added some external organizations that our fictitious limited company, ABC Software Inc, has relationships with.

Adding Employment Metadata

The next task is to define the valid role-pairs between the first and second interactors. In turn, this defines the types of relationships parties can have. Let’s start populating the role_type_relationship metadata table with the company’s employee roles. After all, we can’t create teams without first having workers:

Suppose that ABC Software Inc. is going to hire two employees, Jane Smith and Alex Jones, for the following roles:

Taking a step back in time, you’d see that this relationship started before Jane Smith and Alex Jones were hired; they had to apply for jobs at ABC Software Inc. The relationship would have looked like this:

Are you starting to see the possibilities that the party relationship pattern supports?

We don’t have a table called applicant and another table called employee, as may be found in other models. If you think about it, they would share many of the same attributes – name, address, date of birth, etc; you would have to copy the values from applicant to employee upon successful hire. But have the people involved been transformed from one thing into another? Of course not! They’re still the same people!

In actual fact, it’s only the relationship that’s changed between ABC Software Inc. and Jane Smith or Alex Jones. And this is precisely what the party relationship pattern models.

Continuing On: Project Team Metadata

Before we can create a party_relationship table to define the fact that Jane Smith manages Alex Jones, we must define the project development team’s structure. This is just a question of pairing parent and child roles to form a valid hierarchy:

For all of the above roles, the relationship is read from left to right – e.g. the project manager manages the database engineer. Alternatively, you could adopt the right-to-left format (the database engineer reports to the project manager) if that is your preferred convention.

Finally, we must define the relationship between our two new employees:

Of course you can have any number of teams in the shape of the this hierarchy. In a sense, therefore, party_relationship is an instance of role_type_relationship. This is similar to the way that an object is an instance of a class in OO programming.

Including Logical Metadata

With reference to the project development team diagram, we can also define the following logical relationships between roles:

Note that party_relationship is never an instance of a logical role_type_relationship. So what’s the point of defining logical relationships?

Well, this is probably best explained by way of an example. Imagine that you wanted to send a letter to all employees who are logically members of the support team. To create a mailing list, you would write a query that returns all the LOGICAL Support Team 2^nd interactor roles joined to the same REAL 2^nd interactor roles, joined to party_relationship, joined to the 2^nd interactor party. This would allow you to obtain the names and addresses of all concerned.

A Special Case

You may have noticed a couple of unusual entries in the role_type metadata table, namely:

These are two instances of a special case, which occurs when a party has a reflexive relationship with itself:

For example, for a self-employed system analyst, the 1^st and 2^nd interactors in party_relationship refer back to the same party row – i.e. both foreign key columns contain exactly the same party.ID value.

The Importance of Having Context

Imagine we have a small analytics team that is basically formed from the branch between the project manager and the technical clerk:

Each of the relationships here also exist in the project development team structure. So, how do we differentiate one project manager → system analyst relationship from another?

We use the optional context foreign key between role_type_relationship and role_type. For the small analytics team, we set the context on all the relationships to “small analytics team”, the top-level element. And we do the same kind of thing for the project development team structure. When we traverse the structure, we do so only for the type of team we’re interested in.

Party Relationship BOM Pattern Pros and Cons

If you’ve read my other articles on the subject, you’ve probably guessed that I’m a fan of the Bill of Materials design pattern. It’s simple, but very powerful. The caveat is that it must be used appropriately and it must be tailored so that your implementation remains manageable.

In this party relationship implementation of the BOM pattern, we ensure that our relationships remain accurate by first defining the allowable relationships between the interactors that exist in our domain. This would, for example, prevent the Internal Revenue Service from being “employed” as a web designer at ABC Software Inc.

What possibilities arise from defining relationships in this manner? Well, your organization may need to know what other organizations your current employees and contractors have worked for. This helps avoid possible conflicts of interest or even fraud. An example of this is an awarding organization. It needs to know at which schools its assessors have previously taught to ensure that they don’t assess exam papers from those schools. In a party relationship model, it’s easy to query and obtain that information.

On the other hand, your organization may want to store the same information because it could present business opportunities. It just depends on your domain.

In short, the insights you can get from well-structured party relationship data can be invaluable.