When dealing with relational databases –such as Postgresql, MySQL or AWS RDS– it helps to understand how tables interact with one another. This cheatsheet is is designed to remind me how to connect the dots between different tables using Rails command-line generators.

For the past few years, I've been working with NoSQL exclusively (Heroku and CouchDB). Of course, recently, I was asked to build a Rails app using Heroku and PostgreSQL so I created a little app as a refresher on how to structure relationships.

I went ahead and pushed the Github Repo for anyone interested.

The Fundamentals

There are three main relationships types that will handle 90% of what you need.

One To One (belongs_to)

There are two use cases for one-to-one relationships:

  • You have a unique item and there is only one of them.
  • You want to split a column of data into another table.

For example:

  • A song belongs to an artist.
  • A piece belongs to a composer.
  • A track belongs to an album.
  • An album belongs to an artist.

One To Many (has_many)

Here are a few example statement:

  • An artist has many songs.
  • An artist has many albums.
  • An album has many tracks.
  • An artist has many shows.

Many To Many (has_and_belongs_to_many)

  • An artist has many albums and belongs to many albums.

Simple Demo

I went ahead and created a demo to show how to use Rails command-line to create models and their relationships to each other.

Getting Started

Download App

I suggest first downloading my Github Repo, opening up terminal and running rails db:wipe. This is a task I've created that will create a new database and populate the tables with artist data.

Install PostgreSQL

You will also need to install a database such as PostgreSQL on your local computer. My preferred way of doing it is using Homebrew. Here are the instructions I use to download, install and start a database.

Populate Data

Warning, the data within this app is not perfect by design. I wanted to simulate what it would be like to work with normal music metadata. From my experience, the holes in the data often remind me of Swiss cheese and it's later my responsibility to fix it. But, for the purposes of this demo, we will still be able to show how these relationships are supposed to work.

Creating Models

Once again, the purpose of this demo is not to show you how to create beautiful looking data, but instead to show you how you can begin building relationships between Models.

This demo describes the relationships between artists, their songs, their albums and the tracks within those albums.

Step 1 - Create an artist.

rails g model Artist name:string

After running rails db:wipe, my database looks something like this.

artist

Step 2 - Create an Album model.

The next step is to show how Albums are products made by artists.

rails g model Album name:String artist:belongs_to

Take note, when you use the belongs_to parameter, ActiveRecord will auto create a foreign key (artist_id) that bridges Album with Artist.

Album Foreign Keys

Next, tracks are often used to hold songs within an album. Therefore, a track is not a song, it is simply a container for a song within an album. Therefore, I've crated a track model that only contains a track number.

The other thing to consider is that some songs can live on different albums. For example, Metallica's "Enter Sandman" is a song that lives on the "Black Album" as well as "S&M". Therefore, a Track model has a real utility.

rails g model Track number:integer album:belongs_to

Track Foreign Keys

A song is a piece of intellectual property that belongs to an artist as well as a track on an album.

rails g model Song name:string duration:string trackable:references{polymorphic}:index

Song Foreign Keys

Modifying Models

In order to keep the integrity of the relationships, you need to go into the models themselves and tweak a few more things. Rails can do a lot but it can't do everything.

Artist.rb

class Artist < ApplicationRecord
  has_many :albums
  has_many :songs
end

Album.rb

class Album < ApplicationRecord
  belongs_to :artist
  has_many :tracks
  has_many :songs, through: :tracks
end

class Track < ApplicationRecord
  belongs_to :album  
  has_one :song
end

Song.rb can exist as both a track on an album and a song for an artist.

class Song < ApplicationRecord
  belongs_to :trackable, polymorphic: true
end

Populating the database

Here's an example of the data packaged as a Seed.rb file.

rails db:seed

Rails Console

Now that we've created the models and their respective associations, here's how we can view our work using rails console.

Start Rails Console.

rails c

Get the first row in the Artist table.

artist = Artist.first

Get the first album from the artist.

album = artist.albums.first

Get the first track from the first album from artist.

track = album.tracks.first

Get song 1 from track 1 from album 1 from artist.

song = track.song

Get the name of track 1 from album 1 from artist.

song.name

artist.albums.find(1).tracks.find(1).song.name

Removing Records using delete or destroy

Deleting a record will remove the record but not

This will destroy the album as well as the associated tracks.

artist.albums.first.destroy

Note
When you destroy a record, the record will remain in memory but it will be frozen. Use .frozen? to double check your work. If record.frozen? is true, then it means that you've successfully deleted the record from the database.
Source

Reminders

Index

The class/table/model that carries belongs_to stores the foreign_key.

Naming Conventions

  • Model is singular
  • Controllers are pluralized
  • Join tables are pluralized and in alphabetical order.

Resources