Explore

User<>Entity Relationships

What this is:

When your app serves companies that may have employees (or other types of individuals) that will need to have access that company’s data, you need to create a relationship between the Company record and the User record.

Quick note: I like to use the term “Entity” instead of “Company” because this can cover a broader range of organizations that might not technically be a company.

The wrong way:

An entity field on the user record, or a list of users on the entity record.

Why this doesn’t work:

It doesn’t capture any information about the relationship, other than the fact that it exists.

If the relationship ends, the only option is to remove the User from the Company record (or vice versa), which effectively deletes any historical knowledge that the relationship ever existed.

How I do it:

Create an “Relationship (User<>Entity)” data type, w/ these fields:

Field Name

Field Type

DT/OS

List

Notes

Entity

User

Job Title

Optional - your app might not need this.

Role

Optional - if companies using your app have some hierarchy, this might be useful. However, it shouldn’t be confused with “Permission Level”

Permission Level

(see example options below)

Status

(see example options below)

Events

See notes on this here:

Storing Relationship Data

⁠

No results from filter

⁠

This is called a

@Junction Table

⁠

What you do with this data type:

On the User record, you’d have a “Current Entity Relationship” field, which is set to the junction table for their relationship to the entity they’re working with. This allows for:

Easy access to the status and permission level

You would not have this if you just had a “Current Entity” field on the user - then you’d have to do a search for the Relationship record that ties the two together when you want to see this information.

Privacy rules work to give them access to the entity’s data: Current User’s Current Entity Relationship’s Entity is This Entity

Anytime something happens regarding the Relationship record, an

Event Log⁠

record would be created in order to capture the history of this person’s relationship with the entity.

Why my way is better:

My setup allows you to capture more data about the relationship than the sole fact that it exists.

It allows a user to have multiple entity relationships.

It means that a user only has access to one entity’s data at a time.

If the relationship ends, you change the status, but retain all the historical information about the relationship in your database.

Notes:

Multiple Relationships: If a user has multiple entity relationships, you’d need some way for them to switch between their companies. This could be as simple as a dropdown in the app’s top menu bar where the data source is Search for Relationship records where user = current user and Status = Active. When that input changes, it makes a change on the user and sets the “Current Entity Relationship” field to the one they selected. Now they have access to that entity’s data.

When this doesn’t (fully) work: If you need some aggregate view of all of the user’s companies’ data, you need a slightly different setup. (Example: A project management app where a user might have tasks across multiple companies and you want to show them a roundup of all their pending tasks.) For this setup, you would need an additional field on the user that’s a list of their “Active” Relationship records. That field would need to be maintained (see:

@To Maintain

⁠

); if the status of an Relationship record changes to Inactive, it needs to be removed from that field.

Inactive Status: If the status of the Relationship record changes to “Inactive” then that permission record needs to be removed from that user’s Current Relationship field.

Empty “Relationship” Field: In the case that your user’s “Relationship” field is empty, you will need to set up empty states throughout your app that communicates that they don’t have access to any companies, and prompts them to some action (if appropriate).

Privacy Rules Considerations:

It’s not enough to just point to the fact that the Relationship record exists - you also need to ensure that the Relationship record is “Active” - example:

For the Entity data type: Current User’s Current Relationship’s Entity is This Entity and Current User’s Current Relationship’s Status is Active

Even if you’re removing the relationship record from the user’s record when the status is changed to “inactive” this is still a good practice, just to double-secure the company’s data.

Other Applications

This concept can be applied to any other type of relationship in your app. Examples:

In an educational app, Students should have a “Relationship (Student<>Class)” record for any classes they’re enrolled in.

In a dating app, two people who connect with each other will need a similar

@Junction Table

⁠

that captures things like who initiated the connection, the status of the connection, etc.

In an app where adults interact with kids, you’d want a robust relationship system so that parents could block or limit access to specific people.

Option Sets - Example Options

Entity Permission Levels

Entity Permission Levels

Display

Notes

Admin

Full access, can do anything. Every entity must have at least one “Admin” level user.

Regular

Blocked from certain actions.

No results from filter

⁠

Relationship Statuses

Relationship Statuses

Display

Notes

Active

Inactive

If the person no longer works for the company, etc.

No results from filter

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.