Nulls. Can't Live with 'em Can't Live Without 'em

Let’s start with a simple example:

class Company < ActiveRecord::Base

  has_many :users

end

class User < ActiveRecord::Base

  belongs_to :company

end

A Company has many users and a User belongs to a Company. A 1-to-many from Company to User.

However, for this app a User may or may not belong to a Company. In other words a Company is optional for a given User.

Let’s look at our database.

companies (id, name)
users (id, email, password, company_id)

Obviously the users table has a foreign key linking to the companies table. But for Users that don’t have a Company the value for that field will be NULL. To me that seems strange. I don’t like the fact that there exists a row in a table that has a relationship to another table with no value for that relationship. How would it have been created in the first place. Its like having a Comment without a Post, you’d never have a Comment in your database with a NULL value in its post_id field.

One argument is that the value should be NULL because in the object world a User object’s value for its Company would be nil. The database equivalent of nil would be NULL. But let’s try something else out to see where it takes us.

I want to get rid of that company_id foreign key in the users table. I think we’re missing a concept here.

class Employment < ActiveRecord::Base

  belongs_to :user

  belongs_to :company

end

class User < ActiveRecord::Base

  has_one :employment

end

class Company < ActiveRecord::Base
  
  has_many :employments

  has_many :users, :through => :employments

end

There it is Employment. Employment looks a little odd because its a join model but one side of it is a 1-to-1. A User has one Employment and a Company has many Employments and has many Users :through Employments. Its fine because in the database world a 1-to-1 looks exactly like a 1-to-many.

Now to get a User’s Company you’d go

user.employment.company

Maybe I can use has_many :through, to make it feel just like the original design without Employment.

class User < ActiveRecord::Base
  
  has_one :employment

  has_one :company, :through => :employment

end

Nope. Rails doesn’t like it. That sucks.

How about performance?

In the first design without the Employment model,

user.company

would result in 1 join, from the users table to the companies table.

In the second design with the Employment model,

user.employment.company

would result in 2 joins, 1 from the users table to the employments table and 1 from the employments table to the companies table. If you have a web page that lists all your Users and all their Companyies or vice versa then this could get expensive. In that case it would be best to use ActiveRecord::Base#find’s :include parameter to eagerly fetch the other side of the relationship.

Damn NULLs.

Jared Carroll

Hound automatically reviews Ruby, JavaScript, and CoffeeScript code in your GitHub pull requests and comments on style violations. It is free for open source repos and $12/month per private repo.