Party Relationship Pattern. How to Model Relationships

Jean-Marc Reynaud, Freelance Data Modeler

Jean-Marc Reynaud
Freelance Data Modeler

Posted: July 14, 2016

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

1st interactor2nd interactor
Widget Co. Inc. (employer)Manager 1 (employee)
Widget Co. Inc. (employer)Manager 2 (employee)
Widget Co. Inc. (employer)Employee 1 (employee)
Widget Co. Inc. (employer)Employee 2 (employee)
Widget Co. Inc. (employer)Employee 3 (employee)
Widget Co. Inc. (employer)Employee 4 (employee)
Manager 1 (responsible for)Employee 1 (reports to)
Manager 1 (responsible for)Employee 2 (reports to)
Manager 2 (responsible for)Employee 3 (reports to)
Manager 2 (responsible for)Employee 4 (reports to)

A More Sophisticated Model

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

Project Team DevelopmentSource:

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:

Pretty NameParty Type
HM Revenue and Customs (HMRC)Organization
Internal Revenue Service (IRS)Organization
Passport ServiceOrganization
Limited CompanyOrganization
Public Limited CompanyOrganization
CTO EngineeringPerson
Project ManagerPerson
Project SpecialistPerson
System AnalystPerson
Requirement AnalystPerson
Technical ClerkPerson
System AdministratorPerson
Senior Hardware EngineerPerson
Hardware EngineerPerson
Senior Software EngineerPerson
Software EngineerPerson
Database EngineerPerson
Technical SupportPerson
QA ManagerPerson
Web DesignerPerson
Software QA EngineerPerson
Project OfficePerson
Information Security EngineerPerson
Core TeamOrganization
Support TeamOrganization

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:

1st Role Type2nd Role TypeDescription DirectionDescriptionType of Relationship
Limited CompanyCTO EngineeringLTRemploysREAL
Limited CompanyProject ManagerLTRemploysREAL
Limited CompanyProject SpecialistLTRemploysREAL
Limited CompanySystem AnalystLTRemploysREAL
Limited CompanyRequirement AnalystLTRemploysREAL
Limited CompanyTechnical ClerkLTRemploysREAL
Limited CompanySystem AdministratorLTRemploysREAL
Limited CompanySenior Hardware EngineerLTRemploysREAL
Limited CompanyHardware EngineerLTRemploysREAL
Limited CompanySenior Software EngineerLTRemploysREAL
Limited CompanySoftware EngineerLTRemploysREAL
Limited CompanyDatabase EngineerLTRemploysREAL
Limited CompanyTechnical SupportLTRemploysREAL
Limited CompanyQA ManagerLTRemploysREAL
Limited CompanyWeb DesignerLTRemploysREAL
Limited CompanySoftware QA EngineerLTRemploysREAL
Limited CompanyProject OfficeLTRemploysREAL
Limited CompanyInformation Security EngineerLTRemploysREAL
Limited CompanyApplicantLTRselectsREAL

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

Party RelationshipRole Type Relationship
1st Interactor (Organization)2nd Interactor (Person)1st Interactor (Role)2nd Interactor (Role)Description
ABC Software Inc.Jane SmithLimited CompanyCTO Engineeringemploys
ABC Software Inc.Alex JonesLimited CompanyProject Manageremploys

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:

Party RelationshipRole Type Relationship
1st Interactor (Organization)2nd Interactor (Person)1st Interactor (Role)2nd Interactor (Role)Description
ABC Software Inc.Jane SmithLimited CompanyApplicantselects
ABC Software Inc.Alex JonesLimited CompanyApplicantselects

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:

1st Role Type2nd Role TypeDescription DirectionDescriptionType of Relationship
Project Development TeamCTO EngineeringRTLleadsREAL
CTO EngineeringProject ManagerLTRmanagesREAL
Project ManagerSystem AnalystLTRmanagesREAL
Project ManagerSystem AdministratorLTRmanagesREAL
Project ManagerProject SpecialistLTRmanagesREAL
Project ManagerSenior Software EngineerLTRmanagesREAL
Project ManagerTechnical SupportLTRmanagesREAL
Project ManagerWeb DesignerLTRmanagesREAL
Project ManagerSoftware QA EngineerLTRmanagesREAL
Project ManagerProject OfficeLTRmanagesREAL
Project ManagerInformation Security EngineerLTRmanagesREAL
Project ManagerDatabase EngineerLTRmanagesREAL
Project ManagerTechnical SupportLTRmanagesREAL
Project ManagerQA ManagerLTRmanagesREAL
System AnalystRequirement AnalystLTRmanagesREAL
Requirement AnalystTechnical ClerkLTRmanagesREAL
System AdministratorSenior Hardware EngineerLTRmanagesREAL
Senior Hardware EngineerHardware EngineerLTRmanagesREAL
Senior Software EngineerSoftware EngineerLTRmanagesREAL

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:

Party RelationshipRole Type Relationship
1st Interactor (Organization)2nd Interactor (Person)1st Interactor (Role)2nd Interactor (Role)Description
Jane SmithAlex JonesCTO EngineeringProject Managermanages

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:

1st Role Type2nd Role TypeDescription DirectionDescriptionType of Relationship
Core TeamProject SpecialistRTLis member ofLOGICAL
Core TeamSystem AnalystRTLis member ofLOGICAL
Core TeamRequirement AnalystRTLis member ofLOGICAL
Core TeamTechnical ClerkRTLis member ofLOGICAL
Core TeamSystem AdministratorRTLis member ofLOGICAL
Core TeamSenior Hardware EngineerRTLis member ofLOGICAL
Core TeamHardware EngineerRTLis member ofLOGICAL
Core TeamSenior Software EngineerRTLis member ofLOGICAL
Core TeamSoftware EngineerRTLis member ofLOGICAL
Core TeamDatabase EngineerRTLis member ofLOGICAL
Core TeamTechnical SupportRTLis member ofLOGICAL
Core TeamQA ManagerRTLis member ofLOGICAL
Core TeamWeb DesignerRTLis member ofLOGICAL
Core TeamSoftware QA EngineerRTLis member ofLOGICAL
Core TeamProject OfficeRTLis member ofLOGICAL
Core TeamInformation Security EngineerRTLis member ofLOGICAL
Support TeamWeb DesignerRTLis member ofLOGICAL
Support TeamSoftware QA EngineerRTLis member ofLOGICAL
Support TeamProject OfficeRTLis member ofLOGICAL
Support TeamInformation Security EngineerRTLis member ofLOGICAL

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 2nd interactor roles joined to the same REAL 2nd interactor roles, joined to party_relationship, joined to the 2nd 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:

Role TypeParty Type

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

1st Role Type2nd Role TypeDescription DirectionDescriptionType of Relationship
SelfSystem AnalystLTRemployedREAL

For example, for a self-employed system analyst, the 1st and 2nd 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:

1st Role Type2nd Role TypeDescription DirectionDescriptionType of Relationship
Small Analytics TeamProject ManagerRTLleadsREAL
Project ManagerSystem AnalystLTRmanagesREAL
System AnalystRequirement AnalystLTRmanagesREAL
Requirement AnalystTechnical ClerkLTRmanagesREAL

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.


Try our online database modeler. No registration. No commitments.

Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

New SQL Course! Learn how to process trees and graphs in SQL, and how to effectively organize long SQL queries. View course Discover our other courses: