---
title: NULLs. Can't Live with 'em Can't Live Without 'em
teaser: Problems with NULL in Rails associations.
tags: web,rails,postgresql
author: Jared Carroll
published_on: 2007-01-18
---

Let's start with a simple example:

```ruby
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 `User`s 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.

```ruby
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 `Employment`s and has many `User`s :through `Employment`s.
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`.

```ruby
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 `User`s and all their `Company`s 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.