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.
In Ruby, we might have two classes for this application:
Artist. To keep it simple, our
Stage class has three properties:
capacity and our
Artist class has three properties:
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
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:
Note that classes in Ruby are singular and capitalized. In the database, corresponding tables are plural and lower case.
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.
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
Primary keys in a relational database accomplish a few important purposes:
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
@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
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:
stage_idwhich 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.
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