Welcome to Giant Robots Smashing Into Other Giant Robots — a weblog about development, business, design and technology — written by thoughtbot.

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.

About this entry

You're reading an entry on GIANT ROBOTS SMASHING INTO OTHER GIANT ROBOTS, the company weblog of thoughtbot, inc.

Author:: Jared Carroll

Published:: January 18th 09:04 AM

Updated:: September 30th 09:57 AM

Sections:: Development

thoughtbot is hiring

We are hiring web developers and web designers in both Boston and New York, NY.

What are we up to?

We built Shoulda, an eclectic set of additions to Test::Unit; Paperclip to manage uploaded files without hassle; Jester, a REST/ActiveResource client library written in Javascript, and Squirrel, an enhancement for ActiveRecord's find syntax; — amongst some other projects.

Chad (President) and Jon (CTO) co-authored a technical book titled Pro Active Record: Databases with Ruby and Rails, which explores the ins and outs of the ActiveRecord ruby library. You can buy it today at Amazon.com.

About thoughtbot, inc.

We are a small web application development consulting business, with offices in Boston, MA and New York, NY. If you're looking to find a team for your next web development project or your new web application — get in touch.

Eric 01.18.07 / 10AM

I don’t think it necessary to create a join table unless you have more data you need to store. For example if the employment table also stored some type of ‘date’ or job ‘title’.

Also if a user doesn’t have a company user.employment.company – will raise an exception that nil has an undefined method ‘company’

user.company would just return nil.
floyd 01.18.07 / 11AM

I’m pretty sure it’s not worth the tradeoff of complexity involved in another model, but I like the spirit of this strategy for a couple of reasons. First, I can sympathize with your distaste for columns representing nothing, and in a way your devotion to the concept of modeling states. On the other hand, I think this idea makes a fantastic parody of the has_many :through hysteria in the Rails camp. You can model anything, but that doesn’t mean it’s a good idea. Eric’s point remains the best rule of thumb, don’t create an extra model unless you have additional information to store about the state your modeling, and not until then.
matt jankowski 01.18.07 / 02PM

JNF dictates that you use a join model for every relationship, destroying the possibility of a null foreign key forever.

In this case, I’d redefine the problem. For example—convince someone to change the requirements so that a User could conceivably work for lots of different people, and use has_many :through.
floyd 01.18.07 / 04PM

and 50+ models in your application, and absolute job security
Christmas 01.18.07 / 05PM

I just don’t see anything wrong with NULLs. In fact, they can be very useful. In my radio app, I initially gave Songs a “played” boolean, 1 or 0, default to 0. A more informative, and equally simple way of doing this, was to change it to “played_at”, a timestamp that defaults to NULL. Now I know if something is played if it is NOT NULL, and I also get the time it was played. If it’s not, it’s NULL. And I say that this is elegant.
Jacob 01.18.07 / 09PM
If you do want to go with the join model (Employment) as a has_one for the User model, you could try:
```
class User < ActiveRecord::Base
  has_one :employment
  delegate :company, :to => :employment
end 
```
First seen here at scottraymond.net
matt jankowski 01.18.07 / 11PM

I feel like ‘delegate’ is more a way to give your “first class” models access to each other in a shorthand way, rather than chaining association methods one after another. For example, calling ‘reply.forum’ instead of ‘reply.topic.forum’ when Reply belongs_to Topic which belongs_to Forum, when you need to figure out the Forum a Reply to a Topic is in.

In this case (a “second class” join model), unless you’ve eagerly pulled down ‘employment’ when you created ‘user’, doing ‘user.company’ via the delegation will still result in two queries, I believe (one for employment, one for company).

I wonder if ‘delegate’ could be implemented to just pull down a Company right away, by way of a join through Employment – although at that point you’re doing a has_many :through, and you might as well just call it that.
codeFiend 01.19.07 / 06PM
You could do <macro:code> def self.company if self.employment return self.employment.company else return nil end end </macro> in your user model… still an extra sql query, but not as verbose as user.employment.company
richard 01.20.07 / 09AM

I believe that’s exactly what the ‘delegate’ does.
Today / Now

GIANT ROBOTS SMASHING INTO OTHER GIANT ROBOTS

Nulls. Can't live with 'em can't live without 'em

About this entry

thoughtbot is hiring

What are we up to?

About thoughtbot, inc.

9 comments

Recently

Categories

The thoughtbotsphere