Active Record DB Modeling - Ruby on Rails

Question

Ok guys, this might be a little bit confusing so stick with me. Let me start by introducing my tables. Basically I created four tables upon migration.

• Students
• Teachers
• Subjects
• Admin User

Here are my migration files:

Students:

def up
    create_table :students, :id => false do |t|
      t.integer "student_id",:primary_key => true
      t.string "first_name", :limit => 25
      t.string "last_name", :limit => 50
      t.string "email", :default => ' ', :null => false
      t.string "birthday"
      t.string "subjects"
      t.string "teachers"
      t.string "username", :limit => 25
      t.string "password_digest"
      t.timestamps
    end

Teachers:

def up
    create_table :teachers, :id => false do |t|
      t.integer "teacher_id", :primary_key => true
      t.string "first_name"
      t.string "last_name"
      t.string "email", :default => ' ', :null => false
      t.string "birthday"
      t.string "subjects"
      t.string "username", :limit => 25
      t.string "password_digest"
      t.timestamps
    end

Subjects:

def up
  create_table :subjects, :id => false do |t|
    t.integer "subject_id", :primary_key => true
    t.string "subject_name"
    t.timestamps
  end
end

Admin Users:

def up
  create_table :admin_users, :id => false do |t|
    t.integer "admin_user_id", :primary_key => true
    t.string "username", :limit => 25
    t.string "password_digest"
    t.timestamps
  end
end

So now let me get through the explanation. Basically:

• one student can have many teachers
• one teacher can have many students
• one student can have many subjects
• one teacher can teach many subjects
• the admin can access all of this (create, edit, delete)

I created this fields and set them up on my models like this:

class Student < ApplicationRecord

  has_and_belongs_to_many :subjects
  has_and_belongs_to_many :teachers
  has_many :admin_users
  has_secure_password
  self.primary_key = :student_id

end

class Teacher < ApplicationRecord

  has_and_belongs_to_many :subjects
  has_and_belongs_to_many :students
  has_many :admin_users
  has_secure_password

end

class Subject < ApplicationRecord

  has_and_belongs_to_many :students
  has_and_belongs_to_many :teachers
  has_many :admin_users

  # scope :search, lambda { |query| where(["name LIKE ?", "%#{query}%"])}

end

class AdminUser < ApplicationRecord

  has_secure_password

  scope :newest_first, lambda { order("created_at ASC") }
  scope :oldest_first, lambda { order("created_at DESC") }
  # scope :search, lambda { |query| where(["name LIKE ?", "%#{query}%"])}
end

Now I manually inserted data on mysql data record and then tried to pull up the records for student's subjects and teachers form fields.

How can I manage my database effectively esp teachers, students, and subjects??? Is there anything I need to do first to correlate the tables I have? Please help. Sorry for the long post. I am a newbie here. Appreciate your explanation (layman's term) and answers.

Check my models. Do I need to create a separate table to correlate teachers, students, and subjects?

Side Note: When I pull up my students and teachers field it gives me an error ActiveRecord_Associations_CollectionProxy:0x007f9c504361d0 ERROR.

Answer 1

I think this relation should work in your case:

Students:

def up
  create_table :students, :id => false do |t|
    t.integer "student_id",:primary_key => true
    t.string "first_name", :limit => 25
    t.string "last_name", :limit => 50
    t.string "email", :default => ' ', :null => false
    t.string "birthday"
    t.string "subjects"
    t.string "username", :limit => 25
    t.string "password_digest"
    t.timestamps
  end
end

Ref: Generating auto increment with sequence 1001 Teachers:

def up
    create_table :teachers, :id => false do |t|
      t.integer "teacher_id", :primary_key => true
      t.string "first_name"
      t.string "last_name"
      t.string "email", :default => ' ', :null => false
      t.string "birthday"
      t.string "subjects"
      t.string "username", :limit => 25
      t.string "password_digest"
      t.timestamps
    end
end

Subjects:

def up
  create_table :subjects, :id => false do |t|
    t.integer "subject_id", :primary_key => true
    t.string "subject_name"
    t.timestamps
  end
end

Enrolled Subjects:

def up
  create_table :enrolled_subjects, :id => false do |t|
    t.integer "subject_id"
    t.integer "teacher_id"
    t.integer "student_id"
  end
end

Model:

class Student < ApplicationRecord
  has_many :enrolled_subjects
  has_many :subjects, through: :enrolled_subjects
  has_many :teachers, through: :enrolled_subjects    

  def teacher_names
    self.teachers.map(&:first_name).join(", ")
  end
end

class Teacher < ApplicationRecord    
  has_many :enrolled_subjects
  has_many :subjects, through: :enrolled_subjects
  has_many :students, through: :enrolled_subjects
end

class Subject < ApplicationRecord
  has_many :enrolled_subjects
  has_many :students, through: :enrolled_subjects
  has_many :teachers, through: :enrolled_subjects
end

class EnrolledSubject < ActiveRecord::Base
  belongs_to :student, foreign_key: :student_id
  belongs_to :subject, foreign_key: :subject_id
  belongs_to :teacher, foreign_key: :teacher_id
end

Example Repository