Recursive bi-directionnal many-to-many relationship

We recently reworked a big part of Drawbotics' backoffice, and decided to implement a production workflow close to the logic of a Gantt chart. This led us to the creation of 2 relationships linked to the same table. This article describes the problem with more details, and explains the solution that we came up with, which allowed us to only have one intermediate table to handle the two relationships.

Let's tackle this !

In the Gantt logic, a workflow can be parallelized, enabling several teams to work at the same time on the same project.

In order for us to be as close as possible to what's happening in our production center, and since we wanted to optimize those processes, we had to implement a flexible enough architecture.

The goal is to allow one Task to have several Tasks as successors while also allowing several other tasks to act as predecessors of that same task.

We wanted our model to look like this:

class Task < ActiveRecord::Base

  has_many :successors, class_name: self.name
  has_many :predecessors, class_name: self.name

*
*
end  

Obviously, since we have two has_many/has_many relationship, intermediate tables are required as foreign keys in order to have a correct database. But these two relationship are inverse of one another, which will allow us to only have one intermediate table with two columns, one for the predecessor_id and the other one for the successor_id

For example, the following situation :

Can be transcribed into those dependencies :

This is how we designed the TaskDependency table. The migration is quite straightforward once we have this design in mind

class CreateTaskDependency < ActiveRecord::Migration  
  def change

    create_table :task_dependencies do |t|
      t.integer :successor_id
      t.integer :predecessor_id
    end

    add_index :task_dependencies, [:successor_id, :predecessor_id]

    add_foreign_key :task_dependencies, :tasks, column: :successor_id, name: "task_dependencies_sucessor_id_fk"
    add_foreign_key :task_dependencies, :tasks, column: :predecessor_id, name: "task_dependencies_predecessor_id_fk"


  end
end  

The model is also simple since the TaskDependency model has no goal except to be used as an intermediate.

class TaskDependency < ActiveRecord::Base

  belongs_to :successor, class_name: 'Task'
  belongs_to :predecessor, class_name: 'Task'

end  

Finally, we rewrote our dependencies in order to create has_many through dependencies with our newly made foreign key

class Task < ActiveRecord::Base

  has_many :task_dependency_successors, foreign_key: :successor_id, class_name: 'TaskDependency', dependent: :destroy
  has_many :task_dependency_predecessors, foreign_key: :predecessor_id, class_name: 'TaskDependency', dependent: :destroy
 *
 *
end  

This system allows us to rely as heavily as possible on ActiveRecord. The intermediate table is being automatically populated, and we never have to care about its content except for very precise operations. Most of the task-handling is done this way :

task1 = Task.create(name: 'Task1') #=> Task1  
task2 = Task.create(name: 'Task2') #=> Task2  
task1.successors << task2  
task1.successors #=> [task2]  
task2.predecessors #=> [task1]  

Having this done before going into details allowed us to really focus into the sensitive stuff like inserting a task between two existing ones or creating special behaviours. That architecture is also easy to test, we can create the dependencies easily and verify the flow thanks to the predecessors and successors methods.