How to fit an ER model into Rails? (help an old procedural programmer)

Hi guys!

  I am an old procedural programmer learning Rails and object
orientation. :slight_smile:

  I wish to create a little system to help my wife who is a teacher.

  The attached PDF file has the preliminary Entity-Relationship model.

  I am having a rough time trying to put this model into Rails... :frowning:

  Looking at the model I can think about the SQL code necessary. But I
think Ruby and Rails is here to help and speed the development process.
:wink:

  For some examples:

    Who teaches at school "x"?

SELECT people.name FROM people WHERE people.id IN
  (SELECT team_has_teachers.person_id FROM team_has_teachers WHERE
team_has_teachers.team_id IN
    (SELECT teams.id AS team_id FROM teams WHERE teams.school_id =
      (SELECT schools.id AS school_id FROM schools WHERE schools.name =
'x')))

    Who learns from teacher "x"?

SELECT people.name FROM people WHERE people.id IN
  (SELECT team_has_students.person_id FROM team_has_students WHERE
team_has_students.team_id IN
    (SELECT team_has_teachers.team_id FROM team_has_teachers WHERE
team_has_teachers.person_id =
      (SELECT people.id FROM people WHERE people.name = 'x')))

    Who is in team "x"?

SELECT people.name FROM people WHERE people.id IN
  (SELECT team_has_students.person_id FROM team_has_students WHERE
team_has_students.team_id IN
    (SELECT teams.id FROM teams WHERE teams.name = 'x'))

  Any help is welcome!

Best regards,
  Mosoleu

Attachments:
http://www.ruby-forum.com/attachment/8356/ER_model.pdf

Hi guys!

  I am an old procedural programmer learning Rails and object
orientation. :slight_smile:

  I wish to create a little system to help my wife who is a teacher.

  The attached PDF file has the preliminary Entity-Relationship model.

  I am having a rough time trying to put this model into Rails... :frowning:

  Looking at the model I can think about the SQL code necessary. But I
think Ruby and Rails is here to help and speed the development process.

It is rare to have to use SQL directly in Rails. If you have to then
you are probably doing something wrong.

:wink:

  For some examples:

    Who teaches at school "x"?

@school.teachers

SELECT people.name FROM people WHERE people.id IN
  (SELECT team_has_teachers.person_id FROM team_has_teachers WHERE
team_has_teachers.team_id IN
    (SELECT teams.id AS team_id FROM teams WHERE teams.school_id =
      (SELECT schools.id AS school_id FROM schools WHERE schools.name =
'x')))

    Who learns from teacher "x"?

@teacher.pupils
though in pra

SELECT people.name FROM people WHERE people.id IN
  (SELECT team_has_students.person_id FROM team_has_students WHERE
team_has_students.team_id IN
    (SELECT team_has_teachers.team_id FROM team_has_teachers WHERE
team_has_teachers.person_id =
      (SELECT people.id FROM people WHERE people.name = 'x')))

    Who is in team "x"?

@team.members

SELECT people.name FROM people WHERE people.id IN
  (SELECT team_has_students.person_id FROM team_has_students WHERE
team_has_students.team_id IN
    (SELECT teams.id FROM teams WHERE teams.name = 'x'))

  Any help is welcome!

The details are not correct as I have not looked at the ER diagram,
and it is arguable whether you should start with such a diagram in the
first place, but in order to understand the basics of what rails can
do for you I suggest you work right through a good tutorial such as
railstutorial.org, which is free to use online. Also look at the
Rails Guides, particularly ActiveRecord Associations.

Colin

Hi Colin!

  Thanks for your tips. :slight_smile:

  With help of Rails Guides on ActiveRecord Associations I reach this
point:

= Migrations =

class CreateSchools < ActiveRecord::Migration
  def change
    create_table :schools do |t|
      t.string :name

      t.timestamps
    end
  end
end

class CreateTeams < ActiveRecord::Migration
  def change
    create_table :teams do |t|
      t.integer :school_id
      t.string :name

      t.timestamps
    end
  end
end

class CreatePeople < ActiveRecord::Migration
  def change
    create_table :people do |t|
      t.string :name

      t.timestamps
    end
  end
end

class CreateTeachers < ActiveRecord::Migration
  def change
    create_table :teachers, :id => false do |t|
      t.integer :team_id
      t.integer :person_id

    end
    add_index :teachers, [:team_id, :person_id]
  end
end

class CreateStudents < ActiveRecord::Migration
  def change
    create_table :students, :id => false do |t|
      t.integer :team_id
      t.integer :person_id

    end
    add_index :students, [:team_id, :person_id]
  end
end

= Models =

class School < ActiveRecord::Base
  has_many :teams
end

class Team < ActiveRecord::Base
  belongs_to :school

  has_many :teachers
  has_many :people, :through => :teachers

  has_many :students
  has_many :people, :through => :students
end

class Person < ActiveRecord::Base
end

class Teacher < ActiveRecord::Base
  belongs_to :team
  belongs_to :person
end

class Student < ActiveRecord::Base
  belongs_to :team
  belongs_to :person
end

I would not do it like that. I would just have a people table that
contains all the students and teachers, without the separate classes.
Look up self referential associations to see how you can then have
@person.students (which will be people) and @person.teacher. If you
want the classes then you could use STI.

Colin

class Team < ActiveRecord::Base
belongs_to :school
has_many :teachers
has_many :people, :through => :teachers
has_many :students
has_many :people, :through => :students
end

I’m pretty sure your has_many :people won’t work, because you have two of them with different :through clauses. You would have to do something like:

has_many :teacher_people, :class_name => ‘People’, :through => :teachers

has_many :student_people, :class_name => ‘People’, :through => :students

But as you can see, the naming gets awkward pretty quickly, as well as your database. It would be better to do as Colin suggested, and simply have a People table that contains all the teachers and students. If you really want separate classes for the teachers and students, you could use single table inheritance, but it may not be necessary. Then you could simple have a team_members table that linked your teams to people.

class Team < ActiveRecord::Base
belongs_to :school
has_many :team_members

has_many :members, :through => :team_members

end

class Person < ActiveRecord::Base

scope :teachers, where(:person_type => ‘Teacher’)

scope :students, where(:person_type => ‘Student’)

end

Then, you could get members, teachers, and students for teams like this:

team.members

team.members.teachers

team.members.students

Jim