IT Staff

Blog về chuyên ngành IT

Understanding Basic Database Relationships in Rails

leave a comment »

Understanding Basic Database Relationships in Rails

Wed, Dec 26, 2007

Ruby on Rails, SQL

keke nixon erd Understanding Basic Database Relationships in Rails

This short tutorial will be beneficial for you if database relationships and keywords like belongs_to and has_many confuse you, or if you’re trying to find out how relationships are implemented in Rails. As we create a small demonstration project, you’ll see that one beauty of Rails is how it does most of the work gluing everything together, after you’ve supplied it with information about your database’s structure.

But first — why bother learning about relationships? Very simply, they eliminate a major problem called an update anomaly, and they will probably save you disk space. Having info repeated in multiple entries can be problematic. How would you update a mass misspelling? Would you even notice a misspelled entry? Database normalization and multiple intertwined tables (via relationships) can curb this problem. Fortunately, ActiveRecord makes this easy.

For example, if you store the name and location of all your users in the same database table, you might be wasting disk space by having the same information repeated in multiple entries. You would be wasting a lot of space if your clam-cake-vendor-review site has hundreds of users living in “the State of Rhode Island and Providence Plantations.” This can be eliminated by having the locations tied to unique IDs in their own table, and associated to a user by their ID. This also makes renaming a location easy. Changing “the State of Rhode Island and Providence Plantations” to “Ocean State” is only done in one location, once.

Rather than going over all possible types of relationships here, I will be covering the very basics; Enough to help you grasp the main idea and see how it is implemented in Rails. Let’s begin by designing a simple project.

Project Description

Let’s design a basic musician database. Each artist will be one individual with a name, age, and list of songs (not albums). Each song will have a title, duration and fit under one genre. This design is overly simplified and far from realistic. For simplicity’s sake, we are ignoring the fact that an artist may consist of several individuals and may have multiple albums containing multiple songs, and each song, artist and album can fit under a mesh of genres. It might have a tough time competing with MusicBrainz or, but it should be sufficient enough for our purpose.

Go Go Go

Ruby on Rails and your database software (I use MySQL, but SQLite/PostgreSQL work fine) should be installed and functional. For help installing Rails, see the official site and the official Wiki.

(If using an editor such as vim with rails.vim, you may substitute those commands and shortcuts for the console commands I use here)

In the directory you want your project to reside, create a Rails framework named themusic:

$ rails themusic
    create app/controllers
    create app/helpers
    create app/models
    create app/views/layouts
    create app/environments
    ... omitted ...

Head into the newly created themusic directory and open config/database.yml using your favorite text editor. This file tells Rails what database software you’re using and how it can access it. Notice that this file is divided into three parts (environments): Development, test and production. Rails has the ability to use different databases and even database software for each environment. We will stick in the development environment.

In development, any changes you make to the code are immediate – not requiring a server restart to take effect – and errors spit out a detailed stack trace. The trade off? Performance takes a hit.

Fill in your database login information (you’ll likely just have to put in your password) before saving and closing the file. Create a new database named themusic_development. I use PHPMyAdmin, but this can be done in MySQL using the commandline:

$ mysql -u username -h localhost -p
Enter password: (type pass and hit Enter)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1984

mysql> CREATE DATABASE themusic_development;
Query OK, 1 row affected (0.04 sec)

Identifying Entities and Attributes

An entity is any object you are required to keep information on. Examples of entities include customers, orders, departments and items. How many entities are we keeping track of in our example app? Three: Artist, song and genre. Keep in mind that things you don’t need to store data on are not entities. If your business doesn’t require that you keep track of user or customer information, then they don’t have to be entities. Identifying entities isn’t always straightforward, and it’s not uncommon to alter your app after the initial design phase, so don’t sweat this too much right now; Just follow along.

Each entity has attributes. A person entity might have attributes such as name and DOB. If we were writing a stock inventory system, each item entity might have a price, manufacturer, description, and so forth. In our example, we have the following entities and attributes:

  • Artist: Has a name, age and songs.
  • Song: Has a title, duration and fits under one artist and one genre.
  • Genre: Each has a name, and houses many songs.

Here’s an aesthetic entity-relationship diagram (ERD) depicting this. A crow’s foot line is a “has_many” relationship. (You can read it going in both directions as: An artist has many songs, a song belongs to an artist):
erd understanding relationships1 Understanding Basic Database Relationships in Rails
Also note sexy 60′s font.

Generating the Models and Setting Up the Migrations and Relationships

We have defined our entities and their relationships on paper, and now need them implemented in Rails. Let’s generate three models representing our entities:

$ ruby script/generate model Artist
$ ruby script/generate model Song
$ ruby script/generate model Genre

The generate script automatically creates a migration for each model.

Migrations are optional but immensely helpful (and awesome). If you have no clue what I’m talking about, see UnderstandingMigrations and the joy of migrations.

Let’s edit each migration to create the appropriate fields for each table.

(Although not advised, you may use the MySQL console to create the tables and columns instead of using migrations and rake)

Table structure

The artist table will have the following fields:

  • Unique ID: Created automatically by Rails; We don’t need this in the migration file.
  • Name: a string
  • Age: a small integer

Open up the Artist model’s migration file (db/migrate/001_create_artists.rb) and edit it to resemble this:

class CreateArtists < ActiveRecord::Migration
  def self.up
    create_table :artists do |t|
      t.column :name, :string
      t.column :age, :integer

  def self.down
    drop_table :artists

The song table will have the following fields:

  • Title: a string
  • Duration in seconds: an integer
  • Artist: Identified by the artist’s unique ID (integer and cannot be NULL)
  • Genre: Identified by the genre’s unique ID (as above, a NON-NULL integer)

Edit the Song migration (db/migrate/002_create_songs.rb):

class CreateSongs < ActiveRecord::Migration
  def self.up
    create_table :songs do |t|
      t.column :title, :string
      t.column :duration, :integer
      t.column :artist_id, :integer, :null => false
      t.column :genre_id, :integer, :null => false

  def self.down
    drop_table :songs

The genre table will have a name field. Edit that migration (db/migrations/003_create_genres.rb):

class CreateGenres < ActiveRecord::Migration
  def self.up
    create_table :genres do |t|
      t.column :name, :string

  def self.down
    drop_table :genres

Use Rake to apply the migrations to your database. If all goes well, your console should display something like this:

$ rake db:migrate
(in /home/lotus/rails/themusic)
== CreateArtists: migrating ====================
-- create_table(:artists)
   -> 0.0896s
== CreateArtists: migrated (0.0897s) ==============

== CreateSongs: migrating ======================
-- create_table(:songs)
   -> 0.0263s
== CreateSongs: migrated (0.0264s) ================

== CreateGenres: migrating =================
-- create_table(:genres)
   -> 0.0350s
== CreateGenres: migrated (0.0351s) ===========


Rails needs to know how your models are related. We already did this in the design phase earlier above, but we now need to translate it into terms Rails can understand. Each artist will have multiple songs; Rails understands this using the keyword has_many. Edit the artist model (app/models/artist.rb) to look like this (notice that :songs is plural):

class Artist < ActiveRecord::Base
  has_many :songs

This association is pretty self-explanatory when spoken aloud: Artist has many Songs.

Each song will belong to one artist and one genre. In the migration, we specifically defined that artist_id and genre_id must exist, they cannot not be NULL. This means a song must belong to an artist and fit under a genre.

Edit song.rb (notice both params supplied to belongs_to are singular):

class Song < ActiveRecord::Base
  belongs_to :artist
  belongs_to :genre

We can have multiple songs under the same genre. This is a has_many association. Add it to genre.rb:

class Genre < ActiveRecord::Base
  has_many :songs

Interacting with Our App Via Console

Instead of creating a web interface or editing scaffolds, let’s use the Rails console to interact with our application in its current state. The Rails console is a script named ‘console’ in our apps’ script directory. This console gives you low level access to your Ruby on Rails application, which it loads on its own. It’s a powerful debugger, and makes it fast and easy to test out individual components in your program — not to mention it’s fun! It isn’t always practical but suffices perfectly for our purposes now.

Fire it up: ruby script/console. You should get a >> prompt.

The console includes tab-completion (for example, press Artist.f for a list of commands beginning with the letter f), so feel free to poke around and experiment.

Let’s begin by creating an instance of the Artist model. Type the following and hit enter.

>> jb = => 'Joe Bloggs', :age => 36)

We have essentially created a new record in our database, but if you check your artist’s table in the database now, you’ll notice that this entry is non-existent. This is because it only exists in memory at this point. We have not saved it. It doesn’t have a unique ID associated with it. The new_record? method returns true if the model has never been saved. You can access an object’s methods and attributes via the console:

>> jb.new_record?
=> true
=> "Joe Bloggs"
>> jb.age
=> 36
=> nil

Now try this:

>> jb.songs
=> []

WTFMATEOMGMAGIC! []!? An empty array? Do you remember adding a songs attribute? I hope not, ’cause you didn’t! Rails knows that an Artist object can have multiple songs because you defined this association in the Artist model as ‘has_many :songs.’ Each song the artist has will be added to this array.

Notice that the record gets an ID after it is saved:

=> true
=> 1

Save returns true on success. Let’s create a Song instance:

>> tune = => 'Love Me Three Times', :duration => 456)
=> #nil, "title"=>"Love Me Three Times", "genre_id"=>nil, "duration"=>456}, @new_record=true>

Trying to save our song as this point gives us errors:

ActiveRecord::StatementInvalid: Mysql::Error: Column 'artist_id' cannot be null:
... long trace omitted ...

Remember that artist_id and genre_id cannot be left nil. Let’s create some genres on the fly. In practice, this should be done in a migration.

>> => 'Bluegrass').save
=> true
>> => 'Goa Trance').save
=> true
>> => 'Doo Wop').save
=> true
>> => 'Blues Rock').save
=> true
>> => 'Emo').save
=> true

Assign the tune a genre:

>> tune.genre = Genre.find_by_name('Blues Rock')

See that it has been set (don’t worry if your ID differs):

=> "Blues Rock"
>> tune.genre_id
=> 4

Use the Array object’s << operand to append to Joe Blogg’s song array and then save it.

>> jb.songs << tune
=> true


>> jb.songs[0].title
=> "Love Me Three Times"
=> "Joe Bloggs"
>> tune.artist_id
=> 1

Let’s fetch and create an instance of Joe Blogg’s record from the database:

>> bloggs = Artist.find_by_name('Joe Bloggs')

Let’s explore this object further:

=> "Joe Bloggs"
>> bloggs.songs[0].title
=> "Love Me Three Times"
>> bloggs.songs[0]
=> "Blues Rock"

Add a few more artists and songs to the database and then close the console (ctrl+D or type “quit”).

In the Songs table in our database, the genre and artist are stored by ID as opposed to by string. Even if we have a centillion songs by one artist, updating that artist’s info is only done in one place, once – in the record associated with that ID. If Joe Bloggs changes his name to “The Artist Formerly Known As Joe Bloggs,” we only need to change this once, and it will take effect throughout our app.

Creating a Browser Interface

Don’t get stoned (on beer) just yet. You have a basic understanding of why relationships are useful and how they work, but it’s unlikely your application will be restricted to the Rails console. Let’s create a very basic (and hideous) web interface for our music application. Each objective will be followed by details and caveats on its implementation.

We have models, but we also need controllers and views. Controllers handle events from the user, fetch data from models, and generate the output (view) that our users will see and interact with. This is known as the MVC architecture.

By convention, a controller’s name is plural as opposed to a model which is singular. For example: StoriesController, PicturesController, PeopleController, etcetra; Rails adds the ‘Controller’ suffix to the name you give the generate script. Memorizing Rails’ naming conventions can be confusing at first. I advise keeping the excellent pluralize tool on hand.

Objective #1

When the user visits http://domain.cxm/artists, we want them greeted with a list of the artists in our database. Clicking an artist’s name will open a page with that artist’s personal info and list of songs.

Use the following code to generate the first controller:

$ ruby script/generate controller Artists

The index view (what the user will see) needs to have a list of artists from the database. Add the following index method to the new controller in app/controllers/artists_controller.rb:

class ArtistsController < ApplicationController
    def index
        @artists = Artist.find(:all)

find(:all) returns all the records in the Artists table. Create the view file app/views/artists/index.rhtml and add the following to it:

The Music Just Turns Me On

Available artists

      <% @artists.each do |artist| %>

    • <%= link_to, :action => ‘details’, :id => %>

<% end %>

Fire up the server script ($ ruby script/server) and point your browser to http://localhost:3000/artists. This is the default address for a rails application. If all is well with the world, you’ll see this in your browser:

The Music Just Turns Me On

Available artists

In my example I only have one artist; More would better illustrate the code, particularly later below. Clicking on an artist’s name will lead to an “Unknown action” error, as we do not yet have a details method in our ArtistsController. Let’s add that to our controller now:

def details
    @artist = Artist.find_by_id(params[:id])
    @songs = @artist.songs

The details.rhtml view (create it in the same dir as the index view) should look like this:

<%= %> (<%= @artist.age %>)

      <% @songs.each do |song| %>

    1. <%= song.title %> <%= song.duration %> — <%= %>

<% end %>

Refresh (or run the server again if you closed it) and click an artist’s name in your browser to see the details view:

Joe bloggs (36)

  1. Love Me Three Times 456 — Blues Rock

456 is the duration in seconds; Appropriate for storage, but will bring us terrible misfortune from the marketing department. Let’s make it user-friendly in the form of minutes:seconds. Change the songs.each loop above to the following:

      <% @songs.each do |song| %> <% min = song.duration / 60 sec = song.duration % 60 @duration = ‘%d:%.2d’ % [min, sec] %>

    1. <%= song.title %> <%= @duration %> — <%= %>

<% end %>

Check it in your browser:

Joe bloggs (36)

  1. Love Me Three Times 7:36 — Blues Rock


Objective #2

Users should be able to browse via genre. Clicking ‘Blues Rock’ should list every Blues Rock song from every artist in the database; Let’s make this happen.

First, we will make the song genres on the Artist details page links leading to a method called ‘browse’ in a not-yet-existent genres controller.

In details.rhtml, change the >li< … >/li< line to reflect the following:

  • <%= song.title %> <%= @duration %> — <%= link_to, :controller => ‘genres’, :action => ‘browse’, :id => %>

Let’s generate that controller and implement two methods.

$ ruby script/generate controller Genres index browse

Open app/controllers/genres_controller.rb. We have two method skeletons: index and browse. To the browse method’s body, we want to add code that does this:


if an ID was specified
    Fetch the specific genre record associated with that ID
    Redirect user to genre index view (which will have a list of available genres)

Here’s the Ruby code, add it to the controller:

if params.include? :id
    @genre = Genre.find(:first, :conditions => ['id = ?', params[:id]])
    redirect_to :action => 'index'

The find() returns the first (and only, as each genre should be unique) record matching the ID that was passed from the previous page. You can swap that find method with the simpler and easier to read Genre.find_by_id(params[:id]) if you wish.

Generate created an index view and a browse view for us because we passed it the method names when generating the GenresController. Open browse.rhtml, delete the placeholder HTML inside and add the following:

Available <%= %> songs

      <% @genre.songs.each do |song| %> <% min = song.duration / 60 sec = song.duration % 60 @duration = ‘%d:%.2d’ % [min, sec] %>

    1. <%= link_to, :controller => ‘artists’, :action => ‘details’, :id => %> – <%= song.title %> (<%= @duration %>)

<% end %>

Notice the blatant violation of Ruby on Rail’s fundamental Don’t Repeat Yourself (DRY) principle. Having the same code pasted in more than one place is, among other things, sloppy programming. I only did this here because I don’t want to violate the KISS principle. In your real apps, use partials, applications.rb and the application controller to maximize Code Reuse!

Point your browser to an artist’s page and click a genre:

Available Blues Rock songs

  1. Joe Bloggs – Love Me Three Times (7:36)

The user should also be able to browse by all the available genres in the database. The app/views/genres/index.rhtml view (not to be confused with the ArtistController method’s index view) needs a list of genres. Add the following code into the GenresController’s index method:

@genres = Genre.find(:all)

and the following in the genres/index.rhtml view (delete placeholder HTML, if any):

Browse by genre

      <% @genres.each do |cat| %>

    • <%= link_to, :action => :browse, :id => %>

<% end %>

Hit up http://localhost:3000/genres/:

Browse by genre

Objective #3

Give the user the ability to add artists and songs via forms.

Open app/views/artists/index.rhtml and add the following form_tag code right below the h1 header:

<% form_tag :action => :new do %>
    Name: <%= text_field :artist, :name %>
    Age: <%= text_field :artist, :age, :size => 1 %>
    <%= submit_tag 'Add new artist' %>
<% end %>

This snippet creates a form with three elements. A name textbox, an age textbox, and a submit button. ‘:action => :new’ tells the form to direct the user’s input to the ArtistController’s new method. Open app/controllers/artists_controller.rb and add this new method now:

def new
    @newartist = params[:artist]
    redirect_to :action => :index

Here’s a brief overview of how this function works:

Line 1: What is params[:artist]?

I won’t go over params nor forms too thoroughly here. Above in our view, we had “text_field :artist, :name” and “text_field :artist, :age” — The first parameter we passed to both, ‘:artist,’ is the object name. Rails generates a hash with that name and puts each input field’s name (that’s the second part we passed the two text_fields, ‘:name’ and :’age’ respectively) as a key in that hash. The value that key corresponds to is what the user inputed on the form. For example, if we have a form asking the user to tell us about his car:

  • text_field :car, :model
  • text_field :car, :make
  • text_field :car, :year

where the form’s :action parameter points to method :vroom. In the vroom method, we’d have a hash named ‘:car’ available in params (params[:car]). This hash will have three key-value pairs: params[:car][:model], params[:car][:make] and params[:car][:year]. The values of each will be whatever the user provided in the form’s text fields.

Line 2: Why are we passing params[:artist] to the Artist constructor (

We need to create a new Artist record in the database. We do so like this: => ‘whatever’, :age => 123). accepts a hash with the keys :name and :age. @newartist is a hash with both those keys, because that’s what we named our text_fields in the form above. It might be easier to understand knowing we can do this: => @newartist[:name], :age => @newartist[:age]);

@newartist is optional. We can pass params[:artist] directly to In the real world you’d want to check ALL input coming from the user before any further processing.

Line 3: Why are we redirecting to the index method?

By default, Rails would have tried to give the user the new.rhtml view, resulting in an error (we don’t have such a view). We override this behavior by redirecting flow to the index method. The index method runs its course and the user is then served the index.rhtml view. In practice, this means the user is taken to the front page after they click the submit button.

After the changes above, this is what the artist form should now look like:

The Music Just Turns Me On



Available artists

… HTML output omitted …

On the artist details page, let’s implement the ability to add a new song via a form_tag. We need a list of available genres dynamically generated from the database available to the details.rhtml view. This will go into the ArtistController’s details method:

  def details
    @artist = Artist.find_by_id(params[:id])
    @songs = @artist.songs
    @genres = Genre.find(:all)

Open the details.rhtml view and add the following right under the header:

<% form_tag :action => :addsong, :id => do %>
    Title: <%= text_field :song, :title %>
    Duration (seconds): <%= text_field :song, :duration, :size => 3 %>

    <% @genres.each do |cat| %>
        <%= radio_button :song, :genre_id, %><%= %>
    <% end %>
    <%= submit_tag 'Add song' %>
<% end %>

This form will have a title and duration textfield, N(in this example, 5) genre radio buttons and a submit button. The radio_button helper accepts an object name (:song), an attribute name (in this case :genre_id, which can be accessed as params[:song].genre_id) and a tag value for that option (in our case, the numerical ID of the genre). We pass :id to the form_tag helper because we need it to lookup the artist record in the :addsong method, which we will now create. Open artists_controller.rb and add the following in the class body:

def addsong
    # Create new song
    @song =[:song])

    # Find artist and append new song to his current array of songs
    @artist = Artist.find_by_id(params[:id])
    @artist.songs << @song

    redirect_to :action => 'details', :id => params[:id]

Save. Run server script. Be amazed — Unless it doesn’t work; In which case compare your code with mine and make sure there are no typos, paying attention to nested brackets and colons.

The Music Just Turns Me On

Duration (seconds):

Bluegrass Goa Trance Doo Wop Emo Blues rock

Available artists

… HTML output omitted …

Deleting Records: Remember Josh Breckman

Be cautious if you attempt to implement functionality to delete records. Do not use the link_to helper to destroy/delete records! Requests that change the state of a program should be done using POST, not GET. If the user accidently reloads a page, it shouldn’t change anything in the application. Firefox gives you a warning if you try to reload the page when data has been sent via POST.

You can remove records using the Model.destroy method. A safe alternative to link_to is button_to. Read the following links:

Exercises for the 1337 h4x0r

  1. Give your users the ability to add and remove genres.
  2. Break your application by passing it garbage (for example, http://domain.cxm/artists/view/wheeeeeee) and then fix it so it gracefully handles this input by implementing error handling.
  3. Use form_for to add the ability to edit song details.
  4. Use a layout to make ‘Browse by artists’ and ‘Browse by genres’ links universal throughout the app.
  5. Create a website, use Gimp to design an award-winning logo, and then market this and use some of the earnings to order me an Ethanol IV drip.

Thanks to jcanady for the photo.

External links


Written by Xavier

Tháng Tám 3, 2013 lúc 9:57 sáng

Posted in RoR

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập: Logo

Bạn đang bình luận bằng tài khoản Đăng xuất /  Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Đăng xuất /  Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất /  Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất /  Thay đổi )


Connecting to %s

%d bloggers like this: