Self-Referential Tables and Rails Relationships

Uriel Rodriguez

In Rails, an association is a connection between two Active Record models. These models are thought of as tables representing a class of an object.

To quickly recap a class and an instance of that class:

class Astronaut < ActiveRecord::Base
end

neil_armstrong = Astronaut.create(name: “Neil Armstrong”)

In object oriented programming, a class is thought of as a blue print providing the features (known as attributes or properties) to any instance of that class. In Rails associations, this blue print can be seen in table where the attributes of that class are the columns, providing structure for each instance which are represented as entries or rows.

This example show an astronauts table mirroring the class Astronaut:

astronauts table

An exception to note is that the table representation of the Astronaut class has an id column. Normally when using object oriented programming, an instantiation of an object produces an object id which relates to a location in the memory of the computer used to identify and retrieve that instance. But when using a database, which ActiveRecord does as an ORM, the id column is created automatically by the database and assigned to instances of the class, or row entries. This is done to organize, identify and retrieve the instances for data manipulation.

In Rails, an association is a connection between two Active Record models.

So going back to an association. We can create another model which can be called missions.

As an OOP class it would look like:

class Mission < ActiveRecord::Base
end

But our mission needs astronauts, so to create an “association” we would structure our representation of the Mission class in the database as such:

apollo_11 = Mission.create(name: “Apollo 11”, commander_id: 1, second_seat_id: 2)

missions table

Now this is where the “self referential table” comes in. To illustrate the association between the astronaut and mission model, the astronaut table becomes the self-referential table.

So in order for the database to know how the two tables relate to each other, we define the following macros:

class Mission < ActiveRecord::Base
belongs_to :commander, class_name: “Astronaut”
belongs_to :second_seat, class_name: “Astronaut”
end
class Astronaut < ActiveRecord::Base
has_many :missions_as_commander, foreign_key: :commander_id, class_name: “Mission”
has_many :second_seat_astronauts, through: :missions_as_commander

has_many :missions_as_second_seat, foreign_key: :second_seat_id, class_name: “Mission”
has_many :commander_astronauts, through: :missions_as_second_seat
end

In making sense of these relationships, first what the Mission class macros are expressing is that an instance of a mission or a row in the missions table, is created with one astronaut identified as the commander astronaut by the commander_id attribute and another astronaut identified as the second_seat astronaut by the second_seat_id attribute. Within the context of a mission, each astronaut in the astronauts table can be identified as a variation of the two types of astronauts.

The Astronaut class macros are expressing that each instance of an astronaut can have a list of missions or mission instances in which they were the commander astronaut (has_many :missions_as_commander) and this is known through that astronaut’s assignment to the commander_id column in the missions table which is a foreign key in that table (foreign key: :commander_id, class_name: “Mission”).

And because the astronaut can have a list of many missions in which they were the commander astronaut, they also have a list of all the astronauts who were second_seat in those missions, through the instances of missions in which they were assigned to the commander_id column (has_many :second_seat_astronauts, through: :missions_as_commander).

And so then the same relationships applies to the astronaut instance if they were assigned as the second_seat of a mission.

has_many :missions_as_second_seat, foreign_key: :second_seat_id, class_name: “Mission”
has_many :commander_astronauts, through: :missions_as_second_seat

So as you can see, one table, the astronauts table, is referencing itself in order to establish a relationship with other instances within its own table of astronauts.

This is just another form of relationships or associations that can be used when designing domain models and can help eliminate the need for additional joiner tables.