Building User Self-Join Tables and Relationships in Rails

In my most recent project for Flatiron School, my partner and I collaborated to create a Harry Potter-themed writing application for students. For our database relationships in a Ruby on Rails backend, we worked with a self-join table with our User class. We had two types of users: teachers and students. The teachers have many students and students belong to a teacher. Throughout the process, we learned about the nuanced complexities of creating a self-join table and its relationships to other models.

What is a Self-Join Table?

A self-join table is one that has a relationship to itself. The table will contain its own foreign key, which will refer to a primary key within that table. Here is an example of how that might look:

While user_id is the primary key in the table, teacher_id is a foreign key that identifies a teacher that a student belongs to. Since Minerva McGonagall and Severus Snape are both teachers, they do not have the teacher_id foreign key. However, the student users will have a teacher_id foreign key that refers to a primary key of a teacher within the same table.

How Do You Set up the Model and Migrations?

When we set up our User model, we included a has_many/belongs_to relationship within the model itself. (The “optional: true” in the belongs_to statement lets us create a student in the database without having to directly connect to teacher right away.)

class User < ApplicationRecord
   has_many :students, class_name: "User", foreign_key: "teacher_id"
   belongs_to :teacher, class_name:"User", optional: true

The rails migration will look like the following:

class CreateUsers < ActiveRecord::Migration[6.0]
   def change
      create_table :users do |t|
      t.string :username
      t.references :teacher
      t.timestamps
      end
   end
end

The “references” column type creates the “teacher_id” foreign key once the table is migrated. Behind the scenes, Rails forms the self-join relationship between teacher and student. In the Rails console, you can find a teacher user and query for teacher_user_variable.students, or vice versa as student_user_variable.teachers (save the User into the variable first).

How do you Relate a Self-Join Table to other Models?

One of the challenges we had with our app is finding the right Rails language to connect the self-join table with other models. Our app contained the following relationships:

Student has many Assignments, through StudentAssignment.

Assignment has many Students, through StudentAssignment.

Student Assignment belongs to student and assignment.

Teacher has many Announcements, and Announcement belongs to teacher.

The model relationships are illustrated below:

A very important thing to note is that Student_Assignment and Assignment will have the foreign key of student_id and NOT user_id. Likewise, Announcement has the foreign key of teacher_id and NOT user_id. The models will look like the following:

class User < ApplicationRecord
   has_many :students, class_name: "User", foreign_key: "teacher_id"
   belongs_to :teacher, class_name:"User", optional: true
   has_many :student_assignments, foreign_key: "student_id"
   has_many :assignments, through: :student_assignments
   has_many :announcements, foreign_key: "teacher_id"
end

When searching the database, Student Assignment and Assignment will look for the foreign key of “student_id” forming relationships.

class StudentAssignment < ApplicationRecord
   belongs_to :student, foreign_key: "student_id", class_name:"User"
   belongs_to :assignment
end
class Assignment < ApplicationRecord
   has_many :student_assignments
   has_many :students, class_name:"User", foreign_key: "student_id", through: :student_assignments
   has_many :assignment_questions
end

Likewise, Announcement will search for the foreign key of “teacher_id” when establishing a relationship with the User table.

class Announcement < ApplicationRecord
   belongs_to :teacher, foreign_key: "teacher_id", class_name: "User"
end

It took much debugging and trial and error for my partner and I to formulate the correct word choice and syntax to make these relationships. By the end of this process, we learned that making a solid backend was essential in making the app run more smoothly in the frontend. We were more easily able to access the information we needed to through these database relationships.