Lesson Monday

In this lesson, we'll incorporate a many-to-many relationship into our record store application between Artists and Albums. Many-to-many relationships incorporate a new layer of complexity in our applications. Take your time, be patient, and as always, give yourself time to process and absorb these concepts over the next few days and weeks. Then, when we get to Rails, setting up many-to-many relationships will be considerably easier.

Updating the Database

First, we'll need to update our database schema in psql.

\c record_store

CREATE TABLE artists (id serial PRIMARY KEY, name varchar);

CREATE TABLE albums_artists (id serial PRIMARY KEY, artist_id int, album_id int);

DROP DATABASE record_store_test;

CREATE DATABASE record_store_test WITH TEMPLATE record_store;

We create two new tables:

  • an artists table that just has a name and id;
  • a albums_artists join table that has an id as well as foreign keys for both artist_id and album_id.

Finally, we need to drop our test database because it's no longer up-to-date. Then we'll create a new test database that's a copy of our updated record_store database.

Make sure to also update your spec_helper.rb file to require('artist') and delete everything from our new tables between test runs.

Building our Artist Class

We do not need to build a class for our join table. However, we do need to build out an Artist class. Most of the functionality for this class will be very similar to our Album class so we'll only cover new functionality for establishing a many-to-many relationship between Albums and Artists. Before continuing on, build out a completed Artist class with the same functionality as the Album class. This class should include the following methods (along with tests):

  • .all()
  • .clear()
  • .find()
  • #save()
  • #==
  • #delete()

You may also want to add a basic #update() function in place as well for updating the name of an Artist. This code will look nearly identical to the code for the Album class once it is done.

Establishing a Many-to-Many Relationship

We really just need to do two things to fully establish this many-to-many relationship.

  • First, we need to be able to make an association between an Album and an Artist. We'll do this via the update() method. Note that we could also do this through the save() method as well. For simplicity's sake, we'll focus on update().
  • Second, we need to create a new method that will display all the Albums that belong to an Artist. We'll call this method albums(). This will have similar functionality to the songs() method that we call on instances of the Album class. The key difference will be the SQL statement we use.

Creating an Association Between Albums and Artists

Before we write the test, let's think about the functionality we want our application to have. A user should be able to add an Album to an Artist. There are many ways we could allow a user to do this. In this case, we'll let a user type in an album name. If an Album with that name exists, then we'll make the association. Note that there are some potential pitfalls with this approach. For instance, what if there are two Albums with the same name? To keep this method simple, we won't address that problem here. However, we encourage you to modify the method to consider this issue.

Here's our test:

describe('#update') do
  it("adds an album to an artist") do
    artist = Artist.new({:name => "John Coltrane", :id => nil})
    album = Album.new({:name => "A Love Supreme", :id => nil})
    artist.update({:album_name => "A Love Supreme"})

Note that this method is testing that we can make an association between an Album and an Artist. A separate test should be written to make sure that an Artist's name can be updated as well.

Note that our expect line uses the Artist#albums method, which we haven't written yet. We'll add that method soon.

Let's create our new #update method:

def update(attributes)
  if (attributes.has_key?(:name)) && (attributes.fetch(:name) != nil)
    @name = attributes.fetch(:name)
    DB.exec("UPDATE artists SET name = '#{@name}' WHERE id = #{@id};")
  elsif (attributes.has_key?(:album_name)) && (attributes.fetch(:album_name) != nil)
    album_name = attributes.fetch(:album_name)
    album = DB.exec("SELECT * FROM albums WHERE lower(name)='#{album_name.downcase}';").first
    if album != nil
      DB.exec("INSERT INTO albums_artists (album_id, artist_id) VALUES (#{album['id'].to_i}, #{@id});")

Our #update method now takes an attributes hash as an argument. If the :name key is included in the attributes hash and isn't nil, it will update an artist's name.

Else, if :album_name is included and isn't nil, we'll make a database query to retrieve an Album by that name from the database:

album = DB.exec("SELECT * FROM albums WHERE lower(name)='#{album_name.downcase}';").first

It'll be easier for users to make an association if the database query isn't case-sensitive so we add PostgreSQL's lower() method, which takes a string as an argument and lowercases it. Ruby will then downcase the album_name. That way, if a user were to type in Live At The Village Vanguard, it would properly find a name in the albums row for Live at the Village Vanguard. Check out the PostgreSQL documentation to see other available string methods.

If the line of code above doesn't return a result, then an association won't be made. After all, we can't make an association between an instance of Artist and a non-existent Album. If the line of code does return a result, we make another database query that associates the returned album with the artist:

DB.exec("INSERT INTO albums_artists (album_id, artist_id) VALUES (#{album['id'].to_i}, #{@id});")

It's a simple insert statement. The key difference here is that we're inserting values into our albums_artists join table. Note the following syntax: album['id'].to_i. Where is this hash key value of 'id' coming from? Our SQL statement returns a hash with key-value pairs that looks similar to this: {"id"=>"234", "name"=>"A Love Supreme"}. Note that the "id" value in your application will probably vary. To retrieve the ID, we need to get the value of the key album['id'] and then convert it to an integer.

Astute observers might notice another potential issue here. It's possible to make the same association multiple times in our join table. This is an annoying bug and would result in the same Album showing up multiple times when we call Artist#albums. Try coming up with a solution to this bug during your next classwork project.

If we run our test, it won't pass. That's because we haven't written an #albums method yet. We'll do that now.

Returning an Artist's Albums

We could write a new test but it would be exactly the same as the test for our #update functionality. We'll jump right to the new method:

def albums
  albums = []
  results = DB.exec("SELECT album_id FROM albums_artists WHERE artist_id = #{@id};")
  results.each() do |result|
    album_id = result.fetch("album_id").to_i()
    album = DB.exec("SELECT * FROM albums WHERE id = #{album_id};")
    name = album.first().fetch("name")
    albums.push(Album.new({:name => name, :id => album_id}))
  • First we create an empty array to store all the Albums our SQL queries will return.

  • Next we collect the album_ids from our albums_artists join table that have the correct artist_id. Our results variable holds an array of values that represent the ids of the Albums we want to find.

  • Then we iterate through those results. We make sure they are integers, not strings, and then we make a select statement to grab the correct Album based on the album_id.

  • Finally, we push those results to an albums array which we return.

Our test will now pass.

n+1 Queries

This code works and may even appear to be relatively clean and efficient on the surface but there is a significant code smell. The problem is that our method uses an n+1 query, which is horribly inefficient. An n+1 query occurs when we do two things:

  • First, we make a database call to get the rows we need from a join table. This is the "+1" part of an n+1 query and it's not a code smell. We need to query the join table to figure out which albums are associated with this specific artist.
  • Second, we loop through the results, making a query n times for the n rows on the join table that have the associations we're looking for. This is the very stinky part of our code and should generally be avoided.

It would be much cleaner to have our second query look something like this:

SELECT * FROM albums where ID in (17, 114, 928);

This will make just one query to get albums with ids of 17, 114, and 928, not n queries. However, it's a little trickier to implement this code and our databases are currently very small, which is why we are demonstrating the easier way to solve the problem. If you'd like more of a challenge, rewrite your method so it makes just two total queries, not n+1 queries.

We will cover n+1 queries more with Rails and ActiveRecord, which will make the problem easier to address.

Deleting Associations

There's one more important thing we have to do. In a many-to-many relationship, Albums and Artists aren't dependent on each other. However, we still need to delete the association between an Album and an Artist if an Artist is deleted from the database, and vice versa. An Album should never belong to a non-existent Artist, and an Artist shouldn't belong to a non-existent Album. It will cause errors in our application if we don't remove these associations.

Let's update Artist first, and start with a test:

describe('#delete') do
  it("deletes all albums_artists join relationships belonging to a deleted artist") do
    artist = Artist.new({:name => "John Coltrane", :id => nil})
    album = Album.new({:name => "A Love Supreme", :id => nil})
    artist.update({:album_name => "A Love Supreme"})
    results = DB.exec("SELECT * FROM albums_artists WHERE album_id = #{album.id};")

We'll get a good fail with this test.

To pass the test, this is how we'll update our Artist#delete method:

def delete
  DB.exec("DELETE FROM albums_artists WHERE artist_id = #{@id};")
  DB.exec("DELETE FROM artists WHERE id = #{@id};")

Next, let's update the Album class. We'll start with a test:

describe('#delete') do
  it("deletes all albums_artists join relationships belonging to a deleted album") do
    artist = Artist.new({:name => "John Coltrane", :id => nil})
    album = Album.new({:name => "A Love Supreme", :id => nil})
    artist.update({:album_name => "A Love Supreme"})
    results = DB.exec("SELECT * FROM albums_artists WHERE artist_id = #{artist.id};")

And here's how we'll update the Album#delete method to pass our test:

  def delete
    DB.exec("DELETE FROM albums WHERE id = #{@id};")
    DB.exec("DELETE FROM songs WHERE album_id = #{@id};") 
    DB.exec("DELETE FROM albums_artists WHERE album_id = #{@id};")

With this code in place, any time we delete either an album or an artist, the the relevant associations on the join table will also be deleted.


In this lesson, we've created a many-to-many relationship between Artists and Albums in our record store application. To do this, we updated our database to include a join table with an artist_id and an album_id. Next, we made changes to the #update and #delete methods and also created a new #albums method as well. In the process, we took a quick look at n+1 queries, which we'll cover more in a future lesson.

Note that we've only added functionality to check an artist's albums and to make the association between an artist and an album via the Artist class. In the next class session, we recommend taking the time to add this functionality to the Album class as well. Ultimately, you will be applying the exact same code and principles we've discussed in this lesson. You'll just be doing so with the Album class instead of the Artist class. This is excellent practice for solidifying these concepts.

In the next lesson, we'll update our Sinatra application to utilize our new many-to-many relationship.

Lesson 16 of 29
Last updated August 7, 2022