6

In some ruby on rails projects, I have seen instances where strings are used instead of a foreign key reference to a lookup table.

I usually code in C#/SQL Server and use lookup tables, but am not particularly experienced in ruby on rails.

So my question is, should I be using strings instead of lookup tables in my ruby on rails web project (mysql and active record)? Is this something specific to ruby on rails, or to dynamically typed languages? Or for some particular circumstances? Or is it just bad database design?

By lookup tables I mean, like the following

UserStatus

Id    Name
1     Active
2     Disabled

User

Id    Username               UserStatusId
1     john.smith@gmail.com   1
1     jane.smith@gmail.com   2

Whereas with just strings

User

Id    Username               UserStatus
1     john.smith@gmail.com   active
1     jane.smith@gmail.com   disabled
  • This is not very typical for Ruby on Rails. Though one may decide in some cases to avoid extra queries (most likely not very efficient, since a db would cache such small lookup tables anyway). Maybe if you have very complex data structures and want to avoid a few extra joins in complex queries?. There are better ways in Rails to do this (eg avoiding a table totally and writing a tableless model with this data in a hash if it's only a few strings anyway). – thorsten müller Feb 26 '14 at 11:33
4

[EDIT] Ruby on Rails 4 enums

Rails 4 supports enums, so you can declare an enum attribute where the values map to integers in the database, but can be queried by name.

class Conversation < ActiveRecord::Base
  enum status: [ :active, :archived ]
end

conversation.archived!
conversation.active? # => false
conversation.status  # => "archived"

Conversation.archived # => Relation for all archived Conversations

Conversation.statuses # => { "active" => 0, "archived" => 1 }

Rails 3 and older

You could implement something like this:

USER_STATUS_VALUES = { 1 => :active, 2 => :disabled }

class UserStatus
  attr_reader :status_id

  def status
    USER_STATUS_VALUES[@status_id]
  end

  def status=(new_value)
    @status_id = USER_STATUS_VALUES.invert[new_value]
    new_value
  end
end

You would use it like this:

my_status = UserStatus.new

my_status.status = :new
puts "status: #{my_status.status}"
puts "status_id: #{my_status.status_id}"

Will return:

status: new

status_id: 1

Note: I could have used 'active' instead of :active but in this case, use of symbols is more appropriate.

  • So store the value in a database as an integer, but use the hashes to display/match them in the code? – John Feb 26 '14 at 16:19
  • Exactly. That way, you use it just like if there was a user_status table in the database. If you're using Rails 4, this is already implemented in ActiveRecord models with enum, see edit. – raviolicode Feb 26 '14 at 16:43
1

should I be using strings instead of lookup tables in my ruby on rails web project (mysql and active record)?

I usually try to avoid lookup tables in my ruby on rails projects. Instead, I tend to store the user representations in the language files, e.g.:

user:
  status: Status        
  status_values:
    1: Active
    2: Disabled

(Or whatever kind of structure you prefer, I am not religious about this.)

Then, in the model file, I only introduce Ruby constants for those values that I actually reference in Ruby code:

class User 
    ...
    STATUS_ACTIVE = 1

    def active?
      self.status == User::STATUS_ACTIVE
    end
end

Here as well, I am not religious about this particular syntax. If you feel like it, you could hide some of these lines with meta-programming. The letters "a-c-t-i-v-e" show up awfully often, so some guys may prefer a more DRY approach. ;)

Is this something specific to ruby on rails, or to dynamically typed languages?

No, I have seen both variants (lookup tables vs. hardcoded enumerations) in all kinds of languages.

Or for some particular circumstances?

This is more like it. For example, if you have an application with a significant chance that these values would change over time; or if you have a multi-language application, it would be crazy to always have to go back to the code to change anything.

But there are definitely cases where it would not make any sense at all to change these lookup tables without also changing the code. In this example, the code presumably has to know when a user is active or not active; i.e., it would not make sense to have some translator nilly-willy add a third value 3: maybe-active without modifying the code to actually implement some behaviour. In this case, having a separate table USER_STATUS would be useless.

In short: if "some guy" should be able to modify these values at any point of time, this is much easier in the DB (maybe with an admin "self-service" functionality in your application). Then, these lookups are proper data of your application. Obviously, some values are obvious candidates for a DB (for example, a list of all ISO country codes in all their variants).

Or is it just bad database design?

Not per se, as shown above.

  • One other thing to perhaps consider is the likely requirement for a reporting system such as a data-warehouse. If you need to also build an ETL process to extract the data from your application database into a DW, life will be easier for you if those values are not in code, but in the DB. Otherwise you will have to manually build those dimensions in your DW. Not great, since if they change (even slowly) you would have to change them in both systems. – rmcsharry Jul 2 at 4:39
-1

You can also use the ActiveHash Gem to create a static Model from a YAML/JSON file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.