Relational Database Concepts

The relational model is older than most of the languages you use. Edgar Codd described it in 1970, and the core idea has barely changed: store facts as simple tables, and let the database combine them when you ask.

If that sounds too plain to matter, it is the reason SQL has outlived every fashion since.

A table is a set of facts#

Each row in a table states one fact. In customers, one row says: this person exists, with this email, living in this city. Each column is one attribute of that fact, and every value in the column has the same type.

SELECT customer_id, full_name, city FROM customers WHERE customer_id <= 3;

 customer_id |  full_name   |  city
-------------+--------------+--------
           1 | Aisha Khan   | Mumbai
           2 | Ben Carter   | Austin
           3 | Chloe Dubois | Lyon
(3 rows)

Two words you will hear: a row is a tuple, a table is a relation. The formal names matter because they carry a rule: a relation is a set, and a set has no order and no duplicates. Rows have no "first" or "third" unless you ask for an order explicitly.

Keys turn rows into identities#

A primary key is the column (or columns) that uniquely identifies a row. customers.customer_id is the primary key. No two rows share it and it is never NULL.

A foreign key is a column whose values must exist as a primary key somewhere else. orders.customer_id points at customers.customer_id.

Notice what the relational model does not do. It does not nest an order inside a customer. It stores the customer's key inside the order and lets you follow the reference.

 customers                          orders
 +-------------+------------+       +----------+-------------+------------+
 | customer_id | full_name  |       | order_id | customer_id | order_date |
 +-------------+------------+       +----------+-------------+------------+
 |      1      | Aisha Khan | <-----|    1     |      1      | 2024-02-01 |
 |      2      | Ben Carter | <--+  |    2     |      1      | 2024-03-15 |
 |      3      | Chloe ...  |    +--|    3     |      2      | 2024-02-10 |
 +-------------+------------+       +----------+-------------+------------+
        primary key                         foreign key -> customers

Because the link is just a value, you can ask questions in any direction. Orders for a customer, customers for an order, customers who never ordered. Nested storage makes one of those cheap and the others painful.

Three kinds of relationships#

One-to-many is the common one. One customer, many orders. The foreign key lives on the "many" side.

Many-to-many needs a third table. An order has many products and a product appears on many orders, so order_items sits between them holding one row per pairing. You cannot store a many-to-many relationship in two tables alone without repeating data.

One-to-one is rare. It usually means one table was split for a reason, such as keeping a large or sensitive column away from the main table. The foreign key is also unique.

Schemas have two meanings#

People use "schema" in two ways, and the confusion costs time.

In general conversation, the schema is the structure of the database: its tables, columns, types and constraints.

In PostgreSQL specifically, a schema is also a namespace inside a database, like a folder for tables. The default one is public.

CREATE SCHEMA reporting;
CREATE TABLE reporting.daily_sales (day DATE PRIMARY KEY, revenue NUMERIC(12,2));

SELECT * FROM reporting.daily_sales;   -- qualified name
SHOW search_path;                       -- "$user", public

When you write FROM customers, PostgreSQL looks through the schemas listed in search_path, in order, and uses the first match. MySQL does not have this idea. There, "schema" and "database" mean the same thing.

The database describes itself#

Metadata is stored in tables you can query. This is a quiet superpower.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;

 column_name |     data_type     | is_nullable
-------------+-------------------+-------------
 product_id  | integer           | NO
 name        | character varying | NO
 category    | character varying | NO
 unit_price  | numeric           | NO
 stock_qty   | integer           | NO
(5 rows)

information_schema is standard and works across databases. PostgreSQL also has its own richer catalogs under pg_catalog.

Why this model lasted#

Four properties explain it.

1. Integrity lives in the database. Types, keys and CHECK rules apply to every client. A new service written in a different language cannot skip them.
2. Queries are declarative. You describe the answer and the planner finds the path, as the first lesson showed.
3. Joins happen at read time. You store each fact once and combine facts when needed. Changing a customer's city is one UPDATE, not a hunt through every order.
4. Transactions give all-or-nothing writes. A transfer either moves the money or does not.

SQL and NoSQL#

"NoSQL" is not one thing. It covers several designs that trade away some relational guarantees to gain something else.

The honest comparison is about trade-offs, not winners. Relational systems enforce a schema on write, support joins and multi-row transactions, and usually scale up before they scale out. Many NoSQL systems accept data first and interpret it on read, scale out easily, and make you design around the queries you expect.

A practical rule: start relational. PostgreSQL stores JSON natively with JSONB, handles tens of millions of rows on one machine, and covers most applications without ever needing anything else. Reach for a specialized store when you can name the specific access pattern that your relational database cannot serve.

Common mistakes#

Storing lists in a column. A tags column holding 'red,blue,green' cannot be indexed meaningfully, cannot be constrained, and breaks the moment a tag contains a comma. Use a child table.

Thinking like a spreadsheet. Spreadsheets mix data, layout and formulas. Tables hold data only. Totals are computed by queries, not stored in the last row.

Relying on row order. The set has no order. SELECT * FROM t today and tomorrow may return the same rows in different sequences.

Using NULL to mean three different things. "Unknown", "not applicable" and "empty" are separate ideas. Decide which one a column means and document it.

Assuming NoSQL means no schema. The schema moves into application code, where it is harder to see and enforce.

Practice#

1. In the course schema, name the primary key of each of the six tables and every foreign key. Draw the arrows on paper.
2. Query information_schema.columns for the orders table. Which columns are nullable?
3. A school needs to record students, classes and which students attend which classes. Sketch the tables. Which one is the junction table, and what is its primary key?
4. Create a schema called scratch, create one table in it, and then query it with and without the schema prefix. Explain the error you get without it, and fix it with SET search_path.