Back to articles list
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 interactor | 2nd 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:
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:
| Pretty Name | Party Type |
|---|---|
| HM Revenue and Customs (HMRC) | Organization |
| Internal Revenue Service (IRS) | Organization |
| Passport Service | Organization |
| Same | Organization |
| Limited Company | Organization |
| Public Limited Company | Organization |
| Applicant | Person |
| Self | Person |
| CTO Engineering | Person |
| Project Manager | Person |
| Project Specialist | Person |
| System Analyst | Person |
| Requirement Analyst | Person |
| Technical Clerk | Person |
| System Administrator | Person |
| Senior Hardware Engineer | Person |
| Hardware Engineer | Person |
| Senior Software Engineer | Person |
| Software Engineer | Person |
| Database Engineer | Person |
| Technical Support | Person |
| QA Manager | Person |
| Web Designer | Person |
| Software QA Engineer | Person |
| Project Office | Person |
| Information Security Engineer | Person |
| Technical | Organization |
| Training | Organization |
| Core Team | Organization |
| Support Team | Organization |
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 Type | 2nd Role Type | Description Direction | Description | Type of Relationship |
|---|---|---|---|---|
| Limited Company | CTO Engineering | LTR | employs | REAL |
| Limited Company | Project Manager | LTR | employs | REAL |
| Limited Company | Project Specialist | LTR | employs | REAL |
| Limited Company | System Analyst | LTR | employs | REAL |
| Limited Company | Requirement Analyst | LTR | employs | REAL |
| Limited Company | Technical Clerk | LTR | employs | REAL |
| Limited Company | System Administrator | LTR | employs | REAL |
| Limited Company | Senior Hardware Engineer | LTR | employs | REAL |
| Limited Company | Hardware Engineer | LTR | employs | REAL |
| Limited Company | Senior Software Engineer | LTR | employs | REAL |
| Limited Company | Software Engineer | LTR | employs | REAL |
| Limited Company | Database Engineer | LTR | employs | REAL |
| Limited Company | Technical Support | LTR | employs | REAL |
| Limited Company | QA Manager | LTR | employs | REAL |
| Limited Company | Web Designer | LTR | employs | REAL |
| Limited Company | Software QA Engineer | LTR | employs | REAL |
| Limited Company | Project Office | LTR | employs | REAL |
| Limited Company | Information Security Engineer | LTR | employs | REAL |
| Limited Company | Applicant | LTR | selects | REAL |
Suppose that ABC Software Inc. is going to hire two employees, Jane Smith and Alex Jones, for the following roles:
| Party Relationship | Role Type Relationship | |||
|---|---|---|---|---|
| 1st Interactor (Organization) | 2nd Interactor (Person) | 1st Interactor (Role) | 2nd Interactor (Role) | Description |
| ABC Software Inc. | Jane Smith | Limited Company | CTO Engineering | employs |
| ABC Software Inc. | Alex Jones | Limited Company | Project Manager | employs |
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 Relationship | Role Type Relationship | |||
|---|---|---|---|---|
| 1st Interactor (Organization) | 2nd Interactor (Person) | 1st Interactor (Role) | 2nd Interactor (Role) | Description |
| ABC Software Inc. | Jane Smith | Limited Company | Applicant | selects |
| ABC Software Inc. | Alex Jones | Limited Company | Applicant | selects |
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 Type | 2nd Role Type | Description Direction | Description | Type of Relationship |
|---|---|---|---|---|
| Project Development Team | CTO Engineering | RTL | leads | REAL |
| CTO Engineering | Project Manager | LTR | manages | REAL |
| Project Manager | System Analyst | LTR | manages | REAL |
| Project Manager | System Administrator | LTR | manages | REAL |
| Project Manager | Project Specialist | LTR | manages | REAL |
| Project Manager | Senior Software Engineer | LTR | manages | REAL |
| Project Manager | Technical Support | LTR | manages | REAL |
| Project Manager | Web Designer | LTR | manages | REAL |
| Project Manager | Software QA Engineer | LTR | manages | REAL |
| Project Manager | Project Office | LTR | manages | REAL |
| Project Manager | Information Security Engineer | LTR | manages | REAL |
| Project Manager | Database Engineer | LTR | manages | REAL |
| Project Manager | Technical Support | LTR | manages | REAL |
| Project Manager | QA Manager | LTR | manages | REAL |
| System Analyst | Requirement Analyst | LTR | manages | REAL |
| Requirement Analyst | Technical Clerk | LTR | manages | REAL |
| System Administrator | Senior Hardware Engineer | LTR | manages | REAL |
| Senior Hardware Engineer | Hardware Engineer | LTR | manages | REAL |
| Senior Software Engineer | Software Engineer | LTR | manages | REAL |
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 Relationship | Role Type Relationship | |||
|---|---|---|---|---|
| 1st Interactor (Organization) | 2nd Interactor (Person) | 1st Interactor (Role) | 2nd Interactor (Role) | Description |
| Jane Smith | Alex Jones | CTO Engineering | Project Manager | manages |
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 Type | 2nd Role Type | Description Direction | Description | Type of Relationship |
|---|---|---|---|---|
| Core Team | Project Specialist | RTL | is member of | LOGICAL |
| Core Team | System Analyst | RTL | is member of | LOGICAL |
| Core Team | Requirement Analyst | RTL | is member of | LOGICAL |
| Core Team | Technical Clerk | RTL | is member of | LOGICAL |
| Core Team | System Administrator | RTL | is member of | LOGICAL |
| Core Team | Senior Hardware Engineer | RTL | is member of | LOGICAL |
| Core Team | Hardware Engineer | RTL | is member of | LOGICAL |
| Core Team | Senior Software Engineer | RTL | is member of | LOGICAL |
| Core Team | Software Engineer | RTL | is member of | LOGICAL |
| Core Team | Database Engineer | RTL | is member of | LOGICAL |
| Core Team | Technical Support | RTL | is member of | LOGICAL |
| Core Team | QA Manager | RTL | is member of | LOGICAL |
| Core Team | Web Designer | RTL | is member of | LOGICAL |
| Core Team | Software QA Engineer | RTL | is member of | LOGICAL |
| Core Team | Project Office | RTL | is member of | LOGICAL |
| Core Team | Information Security Engineer | RTL | is member of | LOGICAL |
| Support Team | Web Designer | RTL | is member of | LOGICAL |
| Support Team | Software QA Engineer | RTL | is member of | LOGICAL |
| Support Team | Project Office | RTL | is member of | LOGICAL |
| Support Team | Information Security Engineer | RTL | is member of | LOGICAL |
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 Type | Party Type |
|---|---|
| Self | Person |
| Same | Organization |
These are two instances of a special case, which occurs when a party has a reflexive relationship with itself:
| 1st Role Type | 2nd Role Type | Description Direction | Description | Type of Relationship |
|---|---|---|---|---|
| Self | System Analyst | LTR | employed | REAL |
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 Type | 2nd Role Type | Description Direction | Description | Type of Relationship |
|---|---|---|---|---|
| Small Analytics Team | Project Manager | RTL | leads | REAL |
| Project Manager | System Analyst | LTR | manages | REAL |
| System Analyst | Requirement Analyst | LTR | manages | REAL |
| Requirement Analyst | Technical Clerk | LTR | manages | REAL |
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.
