These are some guidelines on building databases that you will use with your Ruby on Rails applications. I have seen many of these elsewhere in scattered places, though I’m not sure that I agree with all the other takes on these issues. My intention is to both list good practices for building new schemata and how to handle inheriting legacy structure which diverge from these practices. Anyway here goes:

Create a Primary Key Column

It is a reasonable restriction to insist that tables have single columns for primary keys. If you’ve inherited a poorly designed, then just create a view onto it with such a column. Unlike the claims of many anti-Rails rants, the column does not need to be called id Using

set_primary_key your-other-primary-key-column

works just fine. But if you’re creating a new database, why not call the column id?

Foreign Key Columns

If you’re designing a new database you should follow the convention of naming the foreign_key column as othertablename_id.

If your existing table doesn’t have this please use a view.

Join Tables

Join tables should be named with the convention of firstModelnamePlural_secondModelnamePlural. The columns in the join table should use the convention just mentioned of joining back to the model name tables with columns named otherTableName_id.

Has Many Through Tables

If you are going to build a :has_many :through relationship, the :through model probably has its own attributes. Otherwise you would have done :has_and_belongs_to_many. Remember to give that model table its own :id column.

Parent Reference Columns

If you are using a model with :acts_as_tree the column which refers to the parent entities. You can override this with the :foreign_key option on :acts_as_tree

Pluralized Table Names

Its better practice if you’re creating a new database schema to use pluralized names. But if you’ve inherited a database that doesn’t follow these practices, you can enter the following in your config/environment.rb file.

ActiveRecord::Base.pluralize_table_names = false

Overriding Table Names

More generally, if you just don’t want to use the same name or plural of the same name as your model for your table name. Or if you’re inheriting a database schema, and you want to use a different name for your model than is reflected in your database, use the set_table_name option in your ActiveRecord model definition.

class YourModel < ActiveRecord::Base
  set_table_name "some_table_name_different_than_model_name" 

Use Created_At and Updated_At Columns

If you create columns with these names, Rails will put the date and time of record creation in them for you. Be sure to leave the fields as allowing NULLs. For clarity you should avoid using other columns that have a similar purpose. For example, don’t use a column called “creation_time” that you put values in yourself.

There are also additional column names of created_on and updated_on that will be filled in with the dates only of record creation and modification times. I would tend to stick with created_at and modified_at flavors.

Avoid “Magic Columns” Unintentionally

Besides the columns mentioned there are several others that you could unwittingly use that will cause you problems. Don’t ever use these column names in your tables unless you know about and mean to use their special behavior.