Organisation Hierarchy Model

Looking for input on modeling.
Current users model have a field title foreignKey to separate model and reports_to charfiled what I’m planning to use to create/display a hierarchy.
Im sure the image below explain exactly what the output should be.
CEO->Regional Managers->Supervisors->Team Leads->Employees
Working on multiple updates currently so Im not thinking straight :grin:
Is the title alone enough to achieve this ?
Should reports_to point to a foreignKey title ? or a title should take responsibility to other title’s ?
Did a bit of search on this but no solid example found, any input would be great especially with examples or db diagram.


You can search around this forum for “hierarchy” to see some other discussions on this topic.
(See Some modelling advice - #12 by KenWhitesell, and the external link referenced in that comment.)

In general, there are at least three different mechanisms for representing a hierarchical data structure in a relational database effectively. They all require additional data beyond a Foreign Key relationship.

Personally, I recommend treebeard, because it implements all three of the common algorithms.

Might be making a mistake here :thinking: but I run some testing with my initial idea of

class Title(BaseModel):
    title = models.CharField(_("Title"), max_length=50)

class Employee(AbstractUser):
    title = models.ManyToManyField(Title, verbose_name=_("Title"))
    type = models.ForeignKey(EmploymentType, _("Employment Type"), on_delete=models.CASCADE)
    base = models.ForeignKey(Base, _("Base"), on_delete=models.CASCADE, blank=True, null=True)
    office = models.ForeignKey(Office, _("Office"), on_delete=models.CASCADE, blank=True, null=True)
    report_to_role = models.ForeignKey(Title, _("Report to Role"), related_name="report_to_role", on_delete=models.CASCADE, null=True)

# --------------------Get Coupers title = Field Tech ------------------------------------------
<QuerySet [(1,)]>
# --------------------Field Tech reports to Regional Manager pk:5-----------------------
 <QuerySet [('Regional Manager',)]>
# --------------------Get Everyone who's title is Regional Manager ---------------------
<QuerySet [<Employee: Big Boss>]>

In my test project I only have 3 user, maybe should add 10-20 to see the different relations