What does a “has many” association look like in the database?

I’ve got one of these things and it’s got many of those things.

Rails is expressive like that.

You generate your model, fill out the migration, push a few views together and there you have it – an application.

But what does that “has_many association” actually look like in the database?

It’s pretty simple.

As previously discussed, each model maps to a table in the database. And a table can be viewed as a set of columns with rows of data within them.

When you define a “has many” association you’re really asking Rails to build you a foreign key relation in the database. Not all database engines enforce the foreign key rules (I’m looking at you MySQL) but having them defined at worst gives the database a hint as to what’s going on (so it can optimise for performance and things like that).

So, suppose your has_many association is between a BlogPost and Comment (original, I know).

Firstly, I’m looking ahead to Rails 5 and using ApplicationRecord – at the time of writing it’s not released but it’s best to be prepared.

Secondly, note the “dependent: :destroy” on the has_many relation – this tells Rails that if you delete a BlogPost then it should delete the associated Comments as well. Meaning you don’t end up with orphaned Comments – or an error during deletion if your database properly enforces foreign keys.

Thirdly, you want your migrations to look something like this:

Unlike the BlogPost model, the blog_posts table doesn’t mention comments at all. And the comments table uses a “reference” to :blog_post, with a subsequent call to “add_foreign_key”.

This is what we end up with in the database, once we’ve run the migrations and stuck a bit of data in:

id title contents published_on created_at updated_at
1 Look at my foot It’s amazing! 2016-01-08 2016-01-07 2016-01-08
2 Look at my ears They’re weird 2016-01-09 2016-01-09 2016-01-09

And for comments:

id blog_post_id contents approved_on created_at updated_at
1 1 You smell! null 2016-01-08 2016-01-08
2 1 They turn me on 2016-01-10 2016-01-09 2016-01-09
3 2 It’s true, ears are weird 2016-01-12 2016-01-12 2016-01-12
4 1 What shoes are you wearing? 2016-01-14 2016-01-12 2016-01-12

So if you look at this frankly weird database, you can see that the comments table “references :blog_post” has turned into an integer column called blog_post_id – and for the databases that support it, a foreign key relation is defined linking that column to the blog_posts table.

If I was to run the following code on the console bin/rails c:

So, without any knowledge of set theory or Dr Codd, Rails lets you define relations in your tables while specifying them in relatively (no pun intended) natural-sounding language. Taking advantage of decades of data-storage optimisation for free!

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