Trying to figure out database table relations (Rails / ActiveRecord)

Question

So I am building a web app over the next 2 weeks that aims to connect Music Students with Music Instructors.

This will all be done using Ruby on Rails with ActiveRecord database

I am trying to plan out my database with proper relations and want to make sure everything is good to go before moving forward.

My thought was to have a user to user interaction.

This would include the following columns:

first name (string)

last name (string)

location (string)

mobile (boolean)

bio (text)

email (string)

password (string)

The associations in the models for each would be something like

class Student

has_many :teachers, through: :tutorships

has_many :instruments

Then

class Teacher

has_many :students, through: :tutorships

has_many :instruments

Then a instruments table

instrument (string)

skill_level (integer) *using enum in model to associate with beginner, intermediate & advanced

Model would be (belongs_to (:student / :teacher)

Then the join table between the Users would be a tutorship table:

belongs_to :student

belongs_to :teacher

has_many :lessons

Then a lesson table

belongs_to :tutorship

So, this is what I have so far. My question is, when I am setting up my migrations in Rails, am I going to be missing anything in my tables?

I think I need to be putting something like, t.belongs_to :student but since it is a user to user relationship that feels off.

What am I missing here? I know something is amiss.

I wonder if the tutorship table needs a reference to instruments too. IMHO if both students and teachers might have multiple instruments, then it might be unclear what instrument to teach in a specific tutorship? Or is it up to the tutor to just choose one? — spickermann
Hmm. What do you mean, or better put - how would it be implemented in your view? I was thinking that both student and teacher would have an instrument / skill level tied to them and then they would link up because of a mutual interest. Maybe I am thinking of this incorrectly? — vin_Bin87

Thomas R. Koll Thomas R. Koll · Accepted Answer · 2017-04-29T06:10:16

SQL databases are highly normalized, but you are of course allowed to copy the student and teacher ids into the lesson, thus denormalize, and for performance gains there's a good reason to do that.

Actually there's a case where this is a good idea, think of a teacher being sick and one lesson being taught by a replacement teacher. No need to create a new Tutorship, just assign a different teacher for that one lesson.

Something you are missing is of course timestamps/dates.

Trying to figure out database table relations (Rails / ActiveRecord)

2 Answers