The problem with fields such as ‘type’ in database tables

(written by Lawrence Krubner, however indented passages are often quotes)

Most programmers seem to prefer short, generic names for variables and database columns, but I dislike them since generic names can also be reserved keywords in various languages or systems. Words like “class” or “type” or “status” or “state” or “variable” or “update” or “create” or “model” or a bunch of others might be reserved keywords and using them will get you into trouble.

For my part, I just ran into this problem with Rails. (We’ve an old database that was defined in 2005, and we are just now switching the site over the Ruby On Rails.)

Earlier this week I was writing an app that had models connected to a number of legacy databases. To do this, I followed PragDave’s example of subclassing from ‘gatekeeper’ models that establish separate database connections. Without going into too much detail, here’s an example:

class Finance < ActiveRecord::Base
self.abstract_class = true
establish_connection(
ActiveRecord::Base.configurations["finance_#{ENV['RAILS_ENV']}"]
)
end

class Receipt < Finance
end

class Income< Finance
end
Looks good, but I hit a snag – one of my legacy tables had a ‘type’ column defined. When Rails sees a column named ‘type’ for models that aren’t immediate children of ActiveRecord::Base, it assumes that column holds the class name associated with your model. This is how Rails implements single table inheritance, and if that’s not what you intended, you’ve got a little extra work ahead of you.

Earlier this week I was writing an app that had models connected to a number of legacy databases. To do this, I followed PragDave’s example of subclassing from ‘gatekeeper’ models that establish separate database connections. This minimizes the number of active database connections, and looks good too. Without going into too much detail, here’s an example:

class Finance < ActiveRecord::Base
self.abstract_class = true
establish_connection(
ActiveRecord::Base.configurations["finance_#{ENV['RAILS_ENV']}"]
)
end

class Receipt < Finance
end

class Income< Finance
end
Looks good, but I hit a snag – one of my legacy tables had a ‘type’ column defined. When Rails sees a column named ‘type’ for models that aren’t immediate children of ActiveRecord::Base, it assumes that column holds the class name associated with your model. This is how Rails implements single table inheritance, and if that’s not what you intended, you’ve got a little extra work ahead of you.

When you attempt to do a database operation on that model, your SQL queries will contain an additional clause on your type column. Here’s the output from my development log when I try to invoke Receipt.count:

SQL (0.000) SELECT count(*) as count_all FROM receipts where ( ( receipts.`type` = 'Receipt ) )
I think this implementation of inheritance is kind of cool, but unfortunately for me, my ‘type’ column is strictly for historical purposes. Maybe it holds an acronym, a single letter, who knows. Maybe there’s dozens of types, and I don’t want to write inherited class models for all of them.

Okay, so the solution? Disable single table inheritance by overriding ActiveRecord’s inheritance_column method. Let’s return to our Receipt class:

class Receipt < Finance
def self.inheritance_column
nil
end
end
Okay, that will fix up our queries, but we’re not done yet – the type column is just full of surprises. It turns out that type is a Ruby core method, otherwise known as Object#type. Allow me to demonstrate:

$ ruby script/console
Loading development environment.
>> Receipt.find(:first).type
=> Receipt
Great, all that work, and we still can’t use our type column. Our best plan of action from here is to wrap it with some read/write methods, using a different name like ‘category’ instead. It’s a bit of a cop out, I know, but right now I’m out of ideas until it the method becomes fully deprecated in the next version of Ruby.