How I implement a self referencing model?
How to relate the similar model in a hierarchy?
I've recently been working on an internal project for Leave Management. In this post, I shall explain how I am using self referencing model to relate the entities Employee and Supervisor.
What is a Self Referencing Model?
A Self Referencing Model also known as Recursive Relationships is a model or table that can be both a parent and a dependent. This is a way of organising information either as a hierarchy like Employee and Supervisor or in terms of relations such as person A is married to person B.
What's the aim?
In the Leave Management internal project, I have two types of Employees: regular and supervisor. When retrieving the leave requests, I need to find a way of joining the following data models:
- Supervisor
- Employee
- Leave Requests
The supervisor gets a list of employees - requesting for a leave - for the ongoing month as from the current day. I therefore need a way to relate an employee to the supervisor. Following this approach, I can directly add it in the same table using either a parent or supervisor column.
In the above example, I have two columns to know if a particular employee is either a supervisor or not. This might not be an efficient solution as I have redundant data by having two columns.
I've added the isSupervisor column during the database design time as a means of differentiating between the two types of employees as the supervisor will have additional views on the front-end.
I nevertheless can simply use supervisedBy column to know if the person is a supervisor or not. If the value is null, means the person is only an employee - but if it refers to another employee - then he/she is a Supervisor.
Alternatively, I can use another table, for instance employee_supervisor with the following structure:
employee_supervisor_id, supervisor_id, employee_id
What are some problems encountered?
As I've been developing applications on MongoDB for the past three years or so, I've forgotten the basics of SQL that I mastered during my University years. I know Self Referencing Model is possible with SQL as it's common sense.
To begin with, I didn't know how to describe this situation. Meanwhile, I tried looking directly for this option on Ruby on Rails, which proved more difficult than expected. On scanning several related posts on SQL from StackOverflow, I finally got a better idea of the actual name. The Rails solution was effortless.
My aim was to send both the Employee and Supervisor objects in a leave request record. By using belongs_to :employee [in the model], LeaveRequest.includes(:employee) [in the controller] and leaveRequest.employee [in jBuilder], the leave request object was also included employee - but not the Supervisor.
How I solve this problem in Ruby On Rails?
I added another reference to the Employee class name, belonging to the supervisor column with the supervisor_id. I now think I better understand what the belongs_to keyword means.
In the leave request jBuilder file, I can now reference the Supervisor as in leaveRequest.supervisor.firstname, similar to employee.
In the controller, I have nothing additional to add as the reference to Employee is already present.