Rails: Database Relationships
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.
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.
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
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
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.