2019-11-15

Introduction to SQL - Multiple Tables

Now we have a basic knowledge of databases and SQL. Next, it's time to move into another important topic: Multiple Tables. 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. Of course, 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. So instead, we separated into different tables and connected them most efficiently.

Relationships

Imagine working on a magazine where users have different subscriptions to other products. Other subscriptions could have additional 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

* 3 Tables with their own columns, data types, and constraints
* Customer and Subscription table has their own Primary Key
* Order table has their own Primary Key but, the also have Foreign Keys

Here is where we have to "connect" or "relate" each table with their corresponding relationships. Again, we do this using the Foreign Keys.

Primary Key vs. Foreign Key

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

* order_id for orders
* subscription_id for subscriptions
* customer_id for customers

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

* No value can be NULL.
* Each value must be unique
* A table cannot have more than one Primary Key column.

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, a foreign key in orders and the primary key in customers. That means that Foreign 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 these entity relationships in more detail. But, first, let's try to understand how data will look once we add records.

We must first look at the table Order because it contains the foreign key, and we can follow the right path. So let's follow the green-way:

* Order with id 1 belongs to a customer with id 2, Ana.
* Order with id 1 belongs to a subscription with id 1, The Time Magazine.
* This means that Ana is the owner of the Order with id 1, and she bought a subscription to Time Magazine, and the Order was made at 01-10-2021

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

In this case

* Order with id 2 belongs to a customer with id 1, Justin.
* Order with id 2 belongs to a subscription with id 2, The Economist.
* This means that Justin is the owner of the Order with id 2, and he bought a subscription to The Economist, and the Order was made at 02-02-2022

Creating Database and Multiple Tables

Ok, so far, so good. With this high-level overview, we can understand better how relationships work. So let's start coding. First, we will 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: 
[email protected]:~$ 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 a 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 an 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! We can start doing amazing things with multiple tables with all this data, like Joins!

Next blog post, we'll be doing a 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