Now we have a basic knowledge about databases and SQL. It's time to move into another important topic: Multiple Tables and Joins. As we learned previously, relational databases can “connect”, “relate”, “associate” or “join” two or more tables between them, and that’s the magic behind the relational databases. We can have just one big table in our database with all of the information needed, but it will take a lot of resources and it won’t be very efficient. Instead we started to separate in different tables and then connect them with each other in the most efficient way. 

Let's imagine that we are working on a magazine where users have different types of subscriptions to different products. Different subscriptions could have different properties. each user could have multiple associated information. ASs always first things first, let’s design the database schema with an Entity Relationship Diagram (ERD)

In general terms we have

Here is where we have to “connect” or “relate” each table with their corresponding relationships. We do this using the Foreing Keys

In our tables orders, subscriptions and customers, each has a column that identifies each row of the table

These columns are called Primary Key. Primary Keys have some requirements:

Now, let’s re-check the table Orders

Note that customer_id and subscription_id both appear in this table. When a primary key from one table appears in a different table, it is called a foreign key. In this case we annotate this as FK. In this case they have a descriptive name, but are usually called only as id.

The most common types of joins will join a foreign key of one table with the primary key of another table. 

For example, when we join orders and customers, we join customer_id, which is a foreign key in orders and the primary key in customers. That means that Foreing Key is all the magic behind relational databases!

Let's do something more visually

Now we have connected the tables in a relationship called one to many. In the next blog post we’ll be seeing this entity relationships more in detail. For now, let’s try to understand how data will look once we start adding records

We have to look first at table Order, because it contains the foreing key and we can follow the right path. So let’s follow the green path:

Let’s follow the other path. Let’s called red path

In this case

Ok, so far so good. With this high level overview we can understand better how relationships work. So let's start coding. We’re going to create the database and the tables and columns as we learned previously with PostgreSQL. So let’s do it

➜  ~ sudo su - postgres                 

[sudo] password for daniel: 

postgres@daniel-MS-7A15:~$ psql

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

Type "help" for help.

postgres=# CREATE DATABASE newsletters;

CREATE DATABASE

postgres=# \list

Now let’s move into the new database to start creating our tables

postgres=# \c newsletters

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

You are now connected to database "newsletters" as user "postgres".

Now let’s start creating our tables

newsletters=# CREATE TABLE Customers (

newsletters(# customer_id int NOT NULL UNIQUE PRIMARY KEY,

newsletters(# customer_name TEXT NOT NULL,

newsletters(# address TEXT NOT NULL);

CREATE TABLE

newsletters=# \d customers

                 Table "public.customers"

    Column     |  Type   | Collation | Nullable | Default 

---------------+---------+-----------+----------+---------

 customer_id   | integer |           | not null | 

 customer_name | text    |           | not null | 

 address       | text    |           | not null | 

Indexes:

    "customers_pkey" PRIMARY KEY, btree (customer_id)

Now let’s create Subscriptions table

newsletters=# CREATE TABLE Subscriptions (

newsletters(# subscription_id INT NOT NULL UNIQUE PRIMARY KEY,

newsletters(# description TEXT NOT NULL,

newsletters(# price_per_month FLOAT NOT NULL DEFAULT '1.99',

newsletters(# subscription_length INT NOT NULL DEFAULT '1');

CREATE TABLE

newsletters=# \d subscriptions

                               Table "public.subscriptions"

       Column        |       Type       | Collation | Nullable |         Default          

---------------------+------------------+-----------+----------+--------------------------

 subscription_id     | integer          |           | not null | 

 description         | text             |           | not null | 

 price_per_month     | double precision |           | not null | '1.99'::double precision

 subscription_length | integer          |           | not null | 1

Indexes:

    "subscriptions_pkey" PRIMARY KEY, btree (subscription_id)

And finally we’ll be creating Orders table

newsletters=# CREATE TABLE Orders (

newsletters(# order_id INT NOT NULL UNIQUE PRIMARY KEY,

newsletters(# customer_id INT NOT NULL,

newsletters(# subscription_id INT NOT NULL,

newsletters(# purchase_date TIMESTAMP);

CREATE TABLE

newsletters=# CREATE INDEX idx_customer_id

newsletters-# ON Orders (customer_id)

newsletters-# ;

newsletters=# CREATE INDEX idx_subscription_id

newsletters-# ON Orders (subscription_id);

CREATE INDEX

newsletters=# \d orders

                             Table "public.orders"

     Column      |            Type             | Collation | Nullable | Default 

-----------------+-----------------------------+-----------+----------+---------

 order_id        | integer                     |           | not null | 

 customer_id     | integer                     |           | not null | 

 subscription_id | integer                     |           | not null | 

 purchase_date   | timestamp without time zone |           |          | 

Indexes:

    "orders_pkey" PRIMARY KEY, btree (order_id)

    "idx_customer_id" btree (customer_id)

    "idx_subscription_id" btree (subscription_id)

Finally let’s add some data to each table

Customers Data:

INSERT INTO customers (customer_id, customer_name, address)

VALUES 

(1, 'Allie Rahaim', '123 Broadway'),

(2, 'Jacquline Diddle', '456 Park Ave.'),

(3, 'Lizabeth Letsche', '789 Main St.'),

(4, 'Jessia Butman', '1 Columbus Ave.'),

(5, 'Inocencia Goyco', '12 Amsterdam Ave.'),

(6, 'Bethann Schraub', '29 Monticello'),

(7, 'Janay Priolo', '81 Harrisburg'),

(8, 'Ophelia Sturdnant', '31 Deerfield Ave.'),

(9, 'Eryn Vilar', '56 Morton St.'),

(10, 'Jina Farraj', '100 Bryan Ave.');

My Commands

newsletters=# INSERT INTO customers (customer_id, customer_name, address)

newsletters-# VALUES 

newsletters-# (1,'Allie Rahaim','123 Broadway'),

newsletters-# (2,'Jacquline Diddle','456 Park Ave.'),

newsletters-# (3,'Lizabeth Letsche','789 Main St.'),

newsletters-# (4,'Jessia Butman','1 Columbus Ave.'),

newsletters-# (5,'Inocencia Goyco','12 Amsterdam Ave.'),

newsletters-# (6,'Bethann Schraub','29 Monticello'),

newsletters-# (7,'Janay Priolo','81 Harrisburg'),

newsletters-# (8,'Ophelia Sturdnant','31 Deerfield Ave.'),

newsletters-# (9,'Eryn Vilar','56 Morton St.'),

newsletters-# (10,'Jina Farraj','100 Bryan Ave.');

INSERT 0 10

newsletters=# SELECT * FROM customers;

 customer_id |   customer_name   |      address      

-------------+-------------------+-------------------

           1 | Allie Rahaim      | 123 Broadway

           2 | Jacquline Diddle  | 456 Park Ave.

           3 | Lizabeth Letsche  | 789 Main St.

           4 | Jessia Butman     | 1 Columbus Ave.

           5 | Inocencia Goyco   | 12 Amsterdam Ave.

           6 | Bethann Schraub   | 29 Monticello

           7 | Janay Priolo      | 81 Harrisburg

           8 | Ophelia Sturdnant | 31 Deerfield Ave.

           9 | Eryn Vilar        | 56 Morton St.

          10 | Jina Farraj       | 100 Bryan Ave.

(10 rows)

Subscriptions Data

INSERT INTO subscriptions (subscription_id, description, price_per_month, subscription_length)

VALUES 

(1, 'Politics Magazine', 10, '12 months'),

(2, 'Politics Magazine', 11, '6 months'),

(3, 'Politics Magazine', 12, '3 months'),

(4, 'Fashion Magazine', 15, '12 months'),

(5, 'Fashion Magazine', 17, '6 months'),

(6, 'Fashion Magazine', 19, '3 months'),

(7, 'Sports Magazine', 11, '12 months'),

(8, 'Sports Magazine', 12, '6 months'),

(9, 'Sports Magazine', 13, '3 months');

My Commands

newsletters=# INSERT INTO subscriptions (subscription_id, description, price_per_month, subscription_length)

newsletters-# VALUES

newsletters-# (1,'Politics Magazine',10,'12'),

newsletters-# (2,'Politics Magazine',11,'6'),

newsletters-# (3,'Politics Magazine',12,'3'),

newsletters-# (4,'Fashion Magazine',15,'12'),

newsletters-# (5,'Fashion Magazine',17,'6'),

newsletters-# (6,'Fashion Magazine',19,'3'),

newsletters-# (7,'Sports Magazine',11,'12'),

newsletters-# (8,'Sports Magazine',12,'6'),

newsletters-# (9,'Sports Magazine',13,'3');

INSERT 0 9

newsletters=# SELECT * FROM subscriptions; subscription_id |    description    | price_per_month | subscription_length 

-----------------+-------------------+-----------------+---------------------

               1 | Politics Magazine |              10 |                  12

               2 | Politics Magazine |              11 |                   6

               3 | Politics Magazine |              12 |                   3

               4 | Fashion Magazine  |              15 |                  12

               5 | Fashion Magazine  |              17 |                   6

               6 | Fashion Magazine  |              19 |                   3

               7 | Sports Magazine   |              11 |                  12

               8 | Sports Magazine   |              12 |                   6

               9 | Sports Magazine   |              13 |                   3

(9 rows)

Orders Data

INSERT INTO orders (order_id, customer_id, subscription_id, purchase_date)

VALUES

(1, 3, 2, '2021-05-14 14:51:53'),

(2, 2, 4, '2021-05-15 17:51:53'),

(3, 3, 4, '2021-05-18 13:51:53'),

(4, 9, 9, '2021-05-21 17:51:53'),

(5, 7, 5, '2021-06-02 12:51:53'),

(6, 8, 2, '2021-06-14 13:51:53'),

(7, 5, 8, '2021-06-14 17:51:53'),

(8, 9, 5, '2021-06-16 19:51:53'),

(9, 4, 4, '2021-06-17 20:51:53'),

(10, 1, 7, '2021-07-18 22:51:53'),

(11, 5, 4, '2021-07-20 17:51:53'),

(12, 3, 2, '2021-09-21 17:51:53'),

(13, 3, 5, '2021-12-01 18:51:53'),

(14, 6, 5, '2021-12-14 16:51:53'),

(15, 1, 2, '2021-12-15 15:51:53'),

(16, 1, 2, '2021-12-16 14:51:53'),

(17, 3, 6, '2021-12-17 13:51:53'),

(18, 3, 8, '2021-12-18 12:51:53'),

(19, 4, 9, '2021-12-19 11:51:53'),

(20, 1, 7, '2021-12-19 10:51:53');

My Commands

newsletters=# INSERT INTO orders (order_id, customer_id, subscription_id, purchase_date)

newsletters-# VALUES

newsletters-# (1,3,2,'2021-05-14 14:51:53'),

newsletters-# (2,2,4,'2021-05-15 17:51:53'),

newsletters-# (3,3,4,'2021-05-18 13:51:53'),

newsletters-# (4,9,9,'2021-05-21 17:51:53'),

newsletters-# (5,7,5,'2021-06-02 12:51:53'),

newsletters-# (6,8,2,'2021-06-14 13:51:53'),

newsletters-# (7,5,8,'2021-06-14 17:51:53'),

newsletters-# (8,9,5,'2021-06-16 19:51:53'),

newsletters-# (9,4,4,'2021-06-17 20:51:53'),

newsletters-# (10,1,7,'2021-07-18 22:51:53'),

newsletters-# (11,5,4,'2021-07-20 17:51:53'),

newsletters-# (12,3,2,'2021-09-21 17:51:53'),

newsletters-# (13,3,5,'2021-12-01 18:51:53'),

newsletters-# (14,6,5,'2021-12-14 16:51:53'),

newsletters-# (15,1,2,'2021-12-15 15:51:53'),

newsletters-# (16,1,2,'2021-12-16 14:51:53'),

newsletters-# (17,3,6,'2021-12-17 13:51:53'),

newsletters-# (18,3,8,'2021-12-18 12:51:53'),

newsletters-# (19,4,9,'2021-12-19 11:51:53'),

newsletters-# (20,1,7,'2021-12-19 10:51:53');

INSERT 0 20

newsletters=# SELECT * FROM orders;

 order_id | customer_id | subscription_id |    purchase_date    

----------+-------------+-----------------+---------------------

        1 |           3 |               2 | 2021-05-14 14:51:53

        2 |           2 |               4 | 2021-05-15 17:51:53

        3 |           3 |               4 | 2021-05-18 13:51:53

        4 |           9 |               9 | 2021-05-21 17:51:53

        5 |           7 |               5 | 2021-06-02 12:51:53

        6 |           8 |               2 | 2021-06-14 13:51:53

        7 |           5 |               8 | 2021-06-14 17:51:53

        8 |           9 |               5 | 2021-06-16 19:51:53

        9 |           4 |               4 | 2021-06-17 20:51:53

       10 |           1 |               7 | 2021-07-18 22:51:53

       11 |           5 |               4 | 2021-07-20 17:51:53

       12 |           3 |               2 | 2021-09-21 17:51:53

       13 |           3 |               5 | 2021-12-01 18:51:53

       14 |           6 |               5 | 2021-12-14 16:51:53

       15 |           1 |               2 | 2021-12-15 15:51:53

       16 |           1 |               2 | 2021-12-16 14:51:53

       17 |           3 |               6 | 2021-12-17 13:51:53

       18 |           3 |               8 | 2021-12-18 12:51:53

       19 |           4 |               9 | 2021-12-19 11:51:53

       20 |           1 |               7 | 2021-12-19 10:51:53

(20 rows)

Awesome! With all of this data we can start doing amazing things with multiple tables, like Joins!

Next blog post we’ll be doing different type of Joins and understand how all of these start to make more sense

I hope you enjoyed this post

Thanks for reading

Daniel Morales