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 Companys 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.