So far we've looked at tables one at a time. But the real power of a relational database isn't in the tables: it's in the relationships between them. This lesson answers the question every beginner asks on seeing GreenStore's schema: "why nine tables and not just one with everything in it?". You'll see what a primary key is and why the course uses numeric id columns, how a foreign key physically prevents an order from a non-existent customer, what happens when you try to delete a record that others depend on, how 1:1, 1:N and N:M cardinalities are represented, and how normalisation breaks a monolithic table down into a set of healthy ones. It's the most conceptual lesson of the module and also the one that will pay off most when you reach the JOINs.

1. The relational model in 10 minutes
2. Primary key: natural versus surrogate
3. Candidate keys and unique keys
4. Foreign keys and referential integrity
5. What happens when you delete or modify a parent: ON DELETE and ON UPDATE
6. Cardinalities: 1:1, 1:N and N:M
7. Practical normalisation: 1NF, 2NF and 3NF
8. When to denormalise on purpose
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

The relational model was proposed by Edgar F. Codd in 1970 and it rests on three surprisingly simple ideas.

An important note: "relation" doesn't mean "relationship between tables". In Codd's terminology, a relation is a table. The connections between tables are called associations or, in practice, are implemented through foreign keys. The clash of names confuses a lot of people.

1. A relation is a set, so there is no order and no conceptual duplicates. If two rows were identical in every column, they'd be the same tuple.
2. Data is related by value, not by pointers. In GreenStore, orders.customer_id = 7 points to customer 7 because the value matches, not because a memory address is stored somewhere. That idea, which looks obvious today, was revolutionary next to the hierarchical and network systems of the 1960s.
3. The structure is independent of access. You can reorganise indexes and storage without changing a single query.

Everything you'll see in module 3 comes straight out of property 2: a JOIN is nothing more than pairing up rows whose values match.

A primary key (PK) is the column —or combination of columns— that uniquely identifies each row of a table. Its three properties:

• Unique: it can't repeat.
• Not null: it can never be NULL.
• Stable: ideally it should never change.

There are two philosophies for choosing one:

The reasons:

1. Uniformity. You know any table is identified by id, and that any foreign key is called <singular_table>_id. Zero surprises.
2. Stability. A customer's email can change; their id can't. If the email were the PK and it changed, every table referencing it would have to be updated in cascade.
3. Efficiency. An INTEGER takes 4 bytes; an email, 30 or 40. Every index and every foreign key benefits.
4. Didactic readability. WHERE customer_id = 7 is infinitely more convenient in a course than WHERE customer_email = 'sofia.moreira@example.pt'.

Important: using a surrogate id doesn't excuse you from declaring the natural key as UNIQUE. In GreenStore, customers.email is UNIQUE even though the PK is id: without that UNIQUE you could register the same customer twice and the database wouldn't complain.

Nothing says the PK has to be a single column. It could be made of several:

That would mean "a product can only appear once in each order". It's a legitimate decision, but GreenStore uses its own id in order_lines for uniformity and because it lets the same product appear in two lines of the same order with different prices or discounts.

• A candidate key is any set of columns that uniquely identifies a row. A table can have several.
• The primary key is the candidate you pick as the official identifier.
• The remaining candidates are declared as unique keys (UNIQUE).

In customers we have two candidates:

The key difference between PRIMARY KEY and UNIQUE:

That detail about NULLs in UNIQUE is surprising: PostgreSQL considers two nulls not to be equal to each other, so a UNIQUE column can have many rows with NULL. If you need the opposite, PostgreSQL 15 introduced UNIQUE NULLS NOT DISTINCT.

A foreign key (FK) is a column holding values that must exist in another table's primary key. It's the mechanism that connects tables and, above all, the one that guarantees there is no orphan data.

In GreenStore:

It reads: "customer_id must correspond to an existing id in customers, and it's mandatory; employee_id must also exist in employees, but it can be left empty".

This is called referential integrity: the database guarantees that references point at something real. And it isn't a recommendation, it's a physical barrier.

GreenStore has 15 customers. If you try to record an order for customer 999:

Read it carefully, because you'll see it many times:

• violates foreign key constraint → you've broken an FK.
• The name orders_customer_id_fkey follows the pattern <table>_<column>_fkey and tells you exactly which one.
• The DETAIL gives you the guilty value (999) and the table where it should exist.

Without this constraint you'd have a phantom order: it would show up in the sales total, but on trying to display the customer's name there'd be nothing there. That kind of inconsistency is devastating in a real system, and it's impossible to introduce here.

Customer 1 (Lucía Martínez Soler) has orders. If you try to delete her:

PostgreSQL refuses: deleting that customer would leave orders pointing at nothing. This default behaviour is called RESTRICT (technically NO ACTION, which is equivalent except in deferred transactions), and it's exactly what you want most of the time.

When declaring an FK you can choose what should happen when the referenced row is deleted (ON DELETE) or its key changes (ON UPDATE).

Applied to GreenStore, the choice isn't arbitrary: each relationship calls for a different action depending on its business meaning.

A mental rule for deciding: ask yourself "does the child row make sense on its own if the parent disappears?". If it doesn't, CASCADE. If it does but loses an optional relationship, SET NULL. If the parent shouldn't be able to disappear while referenced, RESTRICT.

Careful with CASCADE. It's convenient and dangerous: a single DELETE can propagate silently through half a database. Use it only when the relationship is genuine composition (the part doesn't live without the whole), like order_lines with respect to orders.

ON UPDATE works the same way, but it fires when the parent's primary key changes. With surrogate keys it's almost never used, because an auto-incrementing id never changes. That's precisely one of the advantages of surrogate keys over natural ones.

The full syntax for declaring these constraints (CONSTRAINT ... FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE) is studied in lesson 05-01. What matters here is understanding the decision criteria.

Cardinality describes how many rows of one table can be associated with how many of another.

It's the most frequent one. A category has many products; each product belongs to a single category.

How it's implemented: the FK always goes on the "many" side. products.category_id points to categories.id. Never the other way round: if you put a product_id column in categories, only one product would fit per category.

1:N relationships in GreenStore:

An order contains many products, and a product appears in many orders. An N:M relationship can't be implemented directly: there's nowhere to put the FK. The solution is a bridge table (also called an intermediate or association table).

The N:M between orders and products breaks down into two 1:N relationships that converge on order_lines.

And here's the detail that marks out a well-designed bridge table: order_lines isn't just a link, it has data of its own.

That unit_price is a perfect example of deliberate denormalisation (section 8): it duplicates information that's also in products.price, but it's essential because they're different things: one is the current price and the other the historical price that was billed.

Each row of A corresponds to at most one row of B. It's implemented by putting an FK in one of the two tables and additionally declaring it UNIQUE.

GreenStore has no 1:1 relationships, because they're almost never needed: if the correspondence is exact, the natural thing is to merge both tables into one. There are three legitimate cases:

• Separating large columns that are rarely queried (a products_datasheet table with enormous blocks of text).
• Isolating sensitive data with different permissions (an employees_bank_details table).
• Specialisation: a general users table plus users_admin / users_customer tables with exclusive fields.

A table can reference itself. GreenStore has two cases:

Both columns allow NULL, and that null has a precise meaning: manager_id IS NULL identifies the general manager (employee 1, Rosa Alcázar Vives) and referred_by_id IS NULL identifies customers who arrived on their own. Querying these relationships requires a SELF JOIN, which is studied in lesson 03-06.

Normalisation is the process of organising columns into tables to eliminate redundancy and avoid anomalies. It sounds academic, but it's easier to understand by seeing what happens when it isn't done.

Imagine GreenStore kept all its orders in a single table:

The problems are immediate:

Rule: each cell contains a single atomic value; there are no repeating groups.

The products column violates 1NF blatantly: it holds three values in one cell. To fix it you have to pull the products out into rows of their own.

Signs that something violates 1NF:

• Comma-separated lists in a cell.
• Numbered columns: product_1, product_2, product_3.
• A field that sometimes holds one value and sometimes several.

After applying 1NF, each product of each order is a row, which already gives us the seed of order_lines.

Rule: be in 1NF and have no non-key attribute depending on only part of a composite primary key.

After 1NF, our table's key would be (order_id, product_name). But look:

• date depends only on order_id, not on the product.
• customer_name, customer_email and customer_city depend only on order_id.
• category depends only on product_name, not on the order.

These are partial dependencies, and they cause that data to repeat once per order line. The solution is to split:

• What depends on the order → orders table.
• What depends on the product → products table.
• What depends on the combination (quantity, selling price, discount) → order_lines table.

There you have it, derived from scratch: the bridge table from section 6.2.

Rule: be in 2NF and have no non-key attribute depending on another non-key attribute (no transitive dependencies).

In the resulting orders table we'd still have customer_name, customer_email and customer_city. These depend on customer_email (or on the customer in general), not on order_id. It's a transitive dependency: order_id → customer → email.

The solution: extract a customers table and leave only the customer_id reference in orders. In exactly the same way, category in products depends on the category, not on the product: categories is extracted and products.category_id remains.

Applying these three rules to the GreenStore case gets you, almost mechanically, to the course's nine-table schema. That's the answer to the opening question: the schema isn't split on a whim, but because each table groups exactly the data that depends on one and the same thing.

A memorable summary of the three normal forms:

The classic mnemonic: "every non-key attribute must depend on the key, the whole key and nothing but the key". The first part is 1NF/2NF, "the whole key" is 2NF and "nothing but the key" is 3NF.

There are higher normal forms (BCNF, 4NF, 5NF) that solve rarer cases. In professional practice, reaching 3NF covers 95 % of designs.

Normalisation optimises for integrity and for writes. Sometimes you pay a price in read speed, because rebuilding an invoice means combining five tables. Denormalising is introducing redundancy consciously in exchange for performance or historical correctness.

Legitimate cases, with GreenStore examples:

And the golden rule:

Normalise first. Denormalise later, with measurements in hand, and document why.

Denormalising without measuring is the number one cause of inconsistent databases. Every duplicated piece of data is a piece of data that can drift out of sync, and you'll need an explicit mechanism (a trigger, a batch process, application logic) to keep it up to date. Before denormalising, try an index (module 8) or a materialized view (module 10): they usually solve the problem at no cost in integrity.

• Confusing "relation" with "relationship between tables". In Codd's model, a relation is a table.
• Putting the FK on the wrong side of a 1:N. It always goes on the "many" side. Put it on the "one" side and you limit the relationship to a single child.
• Trying to do an N:M without a bridge table. Storing "3,7,12" in a product_ids column violates 1NF, rules out FKs and makes queries impossible.
• Using CASCADE for convenience. A DELETE can propagate much further than you think. Reserve CASCADE for genuine composition relationships.
• Deleting products that have been sold. It destroys the history and trips the FK. Use active = FALSE (a soft delete); that's why the column exists.
• Forgetting the UNIQUE on the natural key. With a surrogate id as the PK, nothing stops a customer's email being duplicated unless you declare it UNIQUE.
• Over-normalising. Splitting a table into seven out of academic purism complicates every query without adding real integrity.
• Denormalising "just in case". Without a measurement to justify it, all you're doing is creating future inconsistencies.
• Tip: index your foreign keys. PostgreSQL creates an index automatically for the PK, but not for FKs. Without that index, JOINs and cascading deletes can be very slow (module 8).
• Tip: draw the diagram before writing DDL. Ten minutes of schema on paper save weeks of migrations.
• Tip: name FKs with the <singular_table>_id pattern. customer_id, product_id, order_id. Consistency makes queries almost write themselves.

For each pair of GreenStore tables, state the cardinality (1:1, 1:N or N:M), where the foreign key goes and which ON DELETE action you'd choose, justifying it:

1. suppliers and products
2. customers and reviews
3. orders and returns
4. customers and products (through reviews)
5. employees with itself

This table violates all three normal forms. Identify which rule it breaks in each case and decompose it into tables normalised to 3NF, indicating primary and foreign keys.

Predict what PostgreSQL answers to each of these operations on the already-loaded GreenStore database, and explain why:

Solution 1

Solution 2

Violations:

Decomposition into 3NF:

And this is how it ends up:

Notice that the list "5, 4" in the first row turns into two separate reviews: 1NF forced us to discover that there were really two facts there, not one.

Solution 3

a) It fails:

GreenStore has 20 products, so 99 doesn't exist. Referential integrity prevents an orphan review from being created.

b) It fails:

Product 1 (Extra virgin olive oil) appears in several order lines, and that FK is declared RESTRICT precisely to protect the billing history. To withdraw it from the catalogue you run UPDATE products SET active = FALSE WHERE id = 1;.

c) It works, and it deletes more than it looks:

Order 20 is removed and, in cascade, its two order lines too (order_lines.order_id is declared ON DELETE CASCADE). It's the perfect example of why CASCADE must be used with care: a single statement has deleted three rows in two tables. If the order had returns attached, they'd disappear as well.

d) It works:

Employee 4 (Óscar Peris Blasco, sales rep) ends up with no manager assigned. The manager_id column allows NULL by design, so no constraint is violated. There would now be two employees with manager_id IS NULL: the general manager (who is one by nature) and this sales rep (who is one because of an organisational change). It's a good reminder that NULL can mean different things in different rows, and of why it's worth documenting its semantics.

This lesson explains the why behind the schema you're about to load:

• The relational model organises data into relations (tables) of tuples (rows) with attributes (columns) over domains (types), and connects information by value, not by pointers: that's where JOINs come from.
• The primary key identifies each row uniquely, not null and stable. GreenStore uses a surrogate id in all nine tables for uniformity, stability and efficiency, without giving up declaring natural keys such as customers.email as UNIQUE.
• The foreign key guarantees referential integrity: PostgreSQL rejects with violates foreign key constraint both inserting a non-existent reference and deleting a referenced parent.
• The ON DELETE actions (RESTRICT, CASCADE, SET NULL) are chosen according to business meaning: CASCADE for order_lines, SET NULL for orders.employee_id, RESTRICT for products that have been sold.
• The cardinalities 1:N (FK on the "many" side), N:M (a bridge table like order_lines, with data of its own) and the reflexive relationships of employees.manager_id and customers.referred_by_id.
• Normalisation up to 3NF, derived from a monolithic orders table, explains why the schema has nine tables; and deliberate denormalisation justifies order_lines.unit_price keeping the historical price.

In the next lesson, The Course Database: GreenStore, all of this becomes tangible: you'll see the full entity-relationship diagram, the table-by-table description with its columns and types, and the ready-to-copy SQL script that creates the nine tables and loads the data you'll use over the remaining eleven modules. By the end of it you'll have the database running on your machine, and from module 2 onwards you'll start querying it for real.