Relational Database and Active Record Basics

A few weeks ago I had my first exposure to relational databases. In this post I’ll go over the basics of relational databases and Active Record by referencing a recent lab that our class worked on.

Our goal was to make a basic web app that mapped the relationships between three classes:

· Artist

· Song

· Genre

It is important to remember that every class/model will be represented by a table. Each row represents a single instance of the class, and the columns represent the various attributes a model can be assigned (name, ID, age, etc.). It is convention to name the model in in a singular tense and the table (that represents multiple instances of the model) as plural — the Artist class has an Artists table.

Note: there may be additional tables required depending on the relationships between the objects, but we will get to that later.

Here is a simplistic overview of the relationships between our three classes:

Artist

• can have many songs
• can have many genres

Song

• can have only one Artists
• can have many genres

Genre

• can have many songs
• can have many artists

By just listing out the fundamentals of each class we can get a clearer idea of their relationships. The easiest relationship to identify is parent/child — it is clear the Artist class is a parent (it can have many songs) and the Song class is a child (it can only have one artist). The parent table will never have the child ID listed — it will always be the child table that lists the parent id. Why is this? Imagine what the artists table looks like. Each row represents one artist. If you want an artist to be associated to multiple songs, you would need to have an additional column for every song. Conversely, because every song only has one artist, it makes sense to store the artist ID in the song table, where one row represents one song.

This doesn’t work! We cannot add a new column every time we need to add a new song to an artist.

The correct way — the child always has the parent ID

In the case of the genres and songs relationship, it works out that a song can have multiple genres, and a genre can have multiple songs. Whenever you have two classes that have a has-many/has-many relationship, you should immediately know that a join table is required. This is for the same reason as outlined above — the genre ID cannot be stored in the song table because one row represents one song, and we do not want to have to add a new column for every genre that a song belongs to. Similarly, the song ID cannot be stored in the genre table because one row represents one genre and we don’t want to have to add a new column for every song that has multiple genres.

The join table will look something like this:

A join table that lists the primary ID’s of instances of Genres and Songs

A join table will contain the primary ID’s of each class. If one song has multiple genres, the song ID will just be repeated and associated with the new ID. The same is true for genres with multiple songs. The ‘ID’ column on the far left is just the respective relationship ID. You can clearly see that row one has a genre with the ID of 2 set to a song with the ID of 4. Row 2 has the same song (ID 4) assigned to a different genre! This allows us to easily store

Artists also have a relationship with Genres, however, it is important to think about the logic behind your model to accurately represent reality. An artist has a genre only through the type of music (songs) that they make. So an Artist has many genres, through songs.

Here is an outline of the relationships of the classes and tables. Note the different pluralization’s depending on the relationship. When you tell Active Record that an Artist has many genres, it uses this to name the method that you will be able to call on the Artist class. You would call Artist.genres to receive an array of multiple genres– not Artist.genre (which would imply a singular relationship).

We know we will need a join table for songs and genres, so we have created a model to represent this — SongGenre. This is singular because it is a model. The table that represents this model must be named as the plural of this, but only on the last word. So the required table name is song_genres.

You’ll note that we say that the SongGenre class belongs to song and a genre. This is because one row on the song_genres table represents one association.