Lesson Weekend

Postgres is a SQL relational database, which means that it uses the Structured Query Language (SQL) and stores data in tables that can be related to one another through the use of primary keys. In this lesson, we'll explore how a relational database works.

Let's use the idea of organizing a music festival as our example for looking at a relational database. We'll imagine that we need an application that will allow us to add stages and artists who will perform on them.

Tables


In Ruby, we might have two classes for this application: Stage and Artist. To keep it simple, our Stage class has three properties: name, location, capacity and our Artist class has three properties: name, members,genre.

Here are the classes for both:

stage.rb
class Stage
  def initialize(attributes)
    @name = attributes.fetch(:name)
    @location = attributes.fetch(:location)
    @capacity = attributes.fetch(:capacity)
  end
end

and

artist.rb
class Artist
  def initialize(attributes)
    @name = attributes.fetch(:name)
    @members = attributes.fetch(:members)
    @genre = attributes.fetch(:genre)
  end
end

In a database, the equivalent of a class is a table. Database tables look just like spreadsheets or HTML tables with rows and columns:

stages table:
stages table

artists table:
artists table

Note that classes in Ruby are singular and capitalized. In the database, corresponding tables are plural and lower case.

Rows and Columns


When we create new objects of a class in Ruby, we assign each property a value in the #initialize method. When we write SQL statements to save data in a database, we do the same thing; we assign values to each property. The equivalent of an object in the database is the row (also called a record) with each property value being stored in a property column.

Here are examples of 3 rows which are storing the data for 3 instances of stages and 3 instances of artists.

stages table
stages table populated

artists table
artists table populated

Primary keys

When rows are added to a database, the first column written is the primary key. This is the unique id for that row on that table which will never be repeated or reused by any other row on that table. This is similar to how we designed our applications in the last course section to never reuse an id. In our example, the "Blue stage" has a primary key of 1 on the stages table and "Pink Martini" has a primary key of 3 on the artists table.

Primary keys in a relational database accomplish a few important purposes:

  • They provide one guaranteed unique identifier for every row to distinguish it from every other row.
  • They offer an easy index for locating a row. For example, in a table that holds 5000 rows, a request to find item 4237 would be easily located by following the numeric order of the primary key. If the same request was made to find "Pink Martini", every name field would have to be compared for a match 4237 times until the match was made (assuming "Pink Martini" had an id of 4237).
  • We can use them to make a simple relationship connector between one table and another related table.

Relationships


Tables are said to be related when they are connected through a primary key. Once again, this is similar to how we set up one-to-many relationships in the last course section. Currently, the stages table and the artists table are unrelated. There is nothing on either table to help us know which artist is playing on which stage.

However, we can do something similar to what we did in the last section: we can create a relationship between stage and artists by adding a stage_id property to artists that stores the primary key of the stage the artist is performing on:

artists table with stage id

Now, we can see that Mandolin Orange and Pink Martini are playing on the Blue stage(id: 1) and Kamasi Washington is playing on the Red stage(id: 2). An artist has not yet been assigned to the Green stage (id: 3). Each object that was previously stored together in the Stage's @artists array is now individually stored on its own row in the artists table and relates to stages through matching the stage_id to the primary key of id on the stages table.

When a primary key of one table is added as a property on another table, it is called a foreign key and is always named the singular name of the table with _id following it. The artists table has two keys:

  • the unique identifier or primary key; and
  • the stage_id which is a foreign key from the stages table.

Over the next several lessons, we'll see how to build, navigate and populate a Postgres database. We'll start in just the database with SQL statements and then use Ruby to communicate to and from our databases.

Terminology


  • Relational database: a collection of linked tables with relationships to each other using primary keys

  • Table: a database unit with rows and columns that stores a collection of similar data (for example, stages or artists); similar to a spreadsheet

  • Row: a record on a database table that holds a single instance of the table and all of its properties

  • Column: a property on a database table that holds the same information for all rows (for example, name)

  • Primary key: the unique id for a database row on a table; usually automatically assigned by the database when the row is created. It is never duplicated or reused on the table

  • Foreign key: the unique id for a row on another table (for example, stage_id is a foreign key on the artists table)

Lesson 2 of 29
Last updated August 7, 2022