We learned how to design and model a database with this nifty database schema tool. First understand that this tool creates a crude abstraction of what a table and its relationships would look like inside a database. If you’re using ActiveRecord (Object Relational Mapper) like me, you’re essentially creating tables (Objects) that contain columns (Attributes) and drawing relationships between rows (Instances-Of-Object) in those tables. 3 very common relationships are: one-to-one, one-to-many, many-to-many.
Check out the 2-tables above. Each have their own unique ID or primary-key. Let’s assume 1-student has_one address, and that 1-address belongs_to 1-student, that’s a one-to-one relationship. Their needs to be a link set-up between the students-table and the addresses-table. We draw this link by creating a foreign-key (student_id) in the addresses-table. Convention tells us that foreign-keys usually live in the table that “belongs_to” another table.
1-student can take many courses, this establishes a one-to-many relationship. A course belongs_to a student and a student has_many courses. In Object Orientated Programming lingo: a single instance of a course belongs_to a single instance of a student, and a single instance of a student has_many courses.
Many-to-many relationships could get pretty complex. If we throw student enrollments into the mix. 1-student can enroll in many courses, meaning 1-student has_many enrollments therefore establishing a one-to-many relationship between the students-table and enrollments-table. Great! But what about establishing the relationship between enrollments and courses? Doesn’t a single enrollment have a course to be enrolled in? And here’s where it begins to get fuzzy.
As shown above, we could create a foreign-key (course_id) inside of our enrollments-table, but as we create more tables in our database and form more relationships we’re going to encounter many problems, problems that are outside the scope of this post. But here’s what our database would look like if we introduce a teachers-table.
A teacher belongs_to enrollments and courses, therefore both have a teacher_id as a foreign key. Look at how sloppy this is beginning to look imagine how sloppy this is going to get when we inevitably start adding more tables like: schools, advisors, sports-teams etc… We need to take a step back and re-think the structure of our database. Things started getting messy when we introduced enrollments. Since we can logically infer that enrollments is going to contain courses, we can turn enrollments into a join table.
Now doesn’t that look a whole lot sexier? Now students can have many courses through enrollments and courses can have many students through enrollments. Enrollments belong to students and also belong to courses, sticking to ActiveRecord convention. As long as we set up this relationship in our app, ActiveRecord will allow us to take an instance of a student and access her courses through enrollments. How about teachers you ask.
This way enrollments ties students and courses together. Join tables are your friend, use them wisely.