Best practices around database Trees with airtable

[Ambroise]

I have a use case which involves a DB Tree design, and I’d like to learn more about best practices, dos and don’ts.

The use case is common: Employee have a single manager, and each employee could have many subordinates.
Also, there is a notion of direct and indirect subordinates and managers.

(image taken from the internet just to help visualise, not specific to my issue)

At this time, we have a “directSupervisor” field, where we store the employee’s supervisor. This matches a “directSubordinates” field.

This is simple with Airtable, and great, as it updates 2-ways.
But, where I struggle is with “indirectSupervisors” and “indirectSubordinates”, it doesn’t seem possible to do something simple to get this information, nor keep it up-to-date 2-ways when changes are made.

Do you have feedback on how to do that? Things to avoid, best practices, etc?
Thank you.

[RussellBishop]

My suggestion would be for two tables:

Staff - everyone listed in your examples
Relationships(?) - a junction to demonstrate who manages who

A record in the second table would look like:

Manager (link to staff)
Relationship type (select: e.g. direct manager of)
Subordinate (link to staff)

From here, you’ll have fields in your Staff tables to demonstrate instances where your Staff is a manager, and when they are the subordinate.