Joins, associations and relations: the database underneath your ActiveRecord models

Have you ever had issues wrapping your head around associations between your models?

Do you need a join model? What is a join model? What is a join?

How do you set this stuff up?

Relational databases are structured into tables. Each one is basically a spreadsheet – a fixed number of columns and a load of rows. And when it comes to linking data in different tables together, you specify relations between those tables.

A well designed database has a “primary key” for every table. In a Rails application the primary key is an auto-incrementing integer but there’s no actual reason for this to be the case. The only theoretical constraint on primary keys is that they are unique – they can be integers, text, GUIDs (globally unique identifiers) or composites (built out of multiple fields taken together). However, some database engines have their own implementation-related constraints on what a primary key can be – which is why Rails uses the always-available integer.

When a piece of data in a table needs to reference another table it has a column that stores the primary key of the row in the other table. To make sure that this data is valid, you can tell the database engine that column X in table Y is supposed to be reference the primary key in table Z – this makes X a foreign key to Z.

Rails represents these relationships by using ActiveRecord associations.

A Rails example would be something like this:

In the database, the tables would probably look something like this:

If your database engine supports foreign keys (and if it doesn’t it shouldn’t be calling itself a relational database) then it will apply constraints on the values that can be entered in the workouts/person_id column. In particular if you tried to set the value for a person_id to 99 it would reject your update as there’s no person with an ID of 99. This gives you extra safety, preventing bad data from getting into your system.

If you want Rails to add these foreign keys in for you (and an index for speedy lookups), you specify it in your migration.

When you load Susan from the database and then ask for Susan’s workouts (@susan.workouts), the database selects all workouts where the person_id matches Susan’s ID – returning two rows of workout data. Likewise, if you had loaded the “lateral jumps” workout from the database and wanted to see which person is supposed to be doing it (@lateral_jumps.person), the database selects one person where the id matches Lateral Jump’s person_id.

So that’s how Rails’ belongs_to and has_many associations work. In fact, Rails’ has_one association works exactly the same way as has_many, but instead of selecting all workouts where the person_id is whatever, it selects the first workout where the person_id is whatever.

The next confusion is how has_many :through associations work – and how they are different from has_and_belongs_to_many associations. But if you know what’s going on in the database, it’s quite clear what the difference is – and when you may want to use one over the other. And we finally get to learn what a join is. However, that’s for another day…

Do you know what to do but not how it works?

Ever wanted to understand why Rails views work the way that they do? Why variables from your controllers are visible inside your views?

Sign up below to get a free 5 part email course on how Ruby on Rails view rendering works and gain a deep understanding of the Rails magic.

We will send you the course, plus the occasional update from this web-site. But no spam, we promise, and it's easy to unsubscribe