Organisation Hierarchy Model

Using Django Using the ORM
1

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.

Thanks

oh
oh850×484 20.3 KB

2

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.

3

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.filter(last_name='Couper').values_list('title')
<QuerySet [(1,)]>
# --------------------Field Tech reports to Regional Manager pk:5-----------------------
queryset.filter(title=1).values_list('report_to_role__title')
 <QuerySet [('Regional Manager',)]>
# --------------------Get Everyone who's title is Regional Manager ---------------------
queryset.filter(title='5')
<QuerySet [<Employee: Big Boss>]>

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