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:

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

and

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:

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

artists table

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:

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.