How I implement a self referencing model?

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.