A Benefit of Rails: Surrogate Keys

Today I helped a client wrestle with a database task which concluded:

. . . We’ll have to watch out inventing new “fake” customers because one day there may be a real customer with our made up ID ūüė¶

This is a problem in lots of apps, but not in Rails.

Rails enforces the “best practice” of giving every table a¬†primary key named¬†id, which is an auto-incrementing integer. But many non-Rails apps do not do this. Instead, they use some real-world data as the primary key. This is called a natural primary key. E.g., a customer’s social security number. There are arguments for and against this. This is a good little wikipedia page about the differences.

One big problem with natural primary keys is that they often turn out to not be as unique as we think. Plus, then we’re¬†put in the position of “faking” the SS number when we want to manually add customers who don’t have one, e.g. “999-….”. (I remember a college I attended which assigned 999- SS numbers to international students.) And this is the hack that the task description¬†refers to.

In contrast to natural keys, the¬†id¬†field is a so-called surrogate key. It intentionally¬†has no meaning in the real world, and so it has no reason to ever change. It doesn’t matter at all what an object’s id is, just so long as it’s unique. And since it gets set up as an auto-incrementing integer, that will be the case.

So, Rails is “opinionated software” and has made the decision for us. Every table will have;

  1. a surrogate key,
  2. named id,
  3. which is an auto-incrementing integer.

And in one swoop, a whole category of potential problems is eliminated. Secondly, this makes it very easy to get up to speed on a new Rails app.

Yes, Rails does support case-insensitive queries

They’re unfortunately just a little buried and a little undocumented. And so conventional wisdom is that Rails doesn’t do case-insensitive finds. But in fact it does:

Example 1: Find all the statutes whose name begins with texas, case-insensitively:

t = Statute.arel_table
Statute.where(t[:name].matches('texas%'))

Continue reading “Yes, Rails does support case-insensitive queries”

. . . it is supremely important that we ensure our data is safe, consistent and reliable. We can dramatically increase these factors by taking full advantage of the tools at hand.

Yes. This is the most critical, important task in software development. A great set of posts, Coding Rails with Data Integrity by Jay Hayes. Part 1, part 2, and part 3.