KAHIBARO
Discord Login Register ☰
☰

9.6. Relationships

Table of Contents

Understanding Relationships in Databases

When you build a backend that uses a relational database, you almost always work with data that is connected. Users own posts, orders contain items, students enroll in courses. These connections are called relationships.

This chapter focuses on how relationships work in relational databases and how to think about them when you design tables. Specific relationship types like one to one, one to many, and many to many have their own chapters, so here we focus on the shared ideas.

Why Relationships Matter

Without relationships, every table would be isolated. You would have to duplicate data everywhere.

Example without relationships:

If a customer changes their address, you would need to update it in three places. If you forget one, your data becomes inconsistent.

With relationships, you can:

Relationships help you:

Core Concepts Behind Relationships

Keys and References

Relationships are built on two core ideas:

  1. Primary keys (covered in another chapter)
    A primary key uniquely identifies a row in a table. For example:
    • users(id, email, name, ...)
    • id could be the primary key.
  2. Foreign keys (covered in its own chapter)
    A foreign key stores the primary key of another table. It creates a link.

For example, if each order belongs to a user:

The relationship is the logical connection between rows in different tables using these keys.

You can think about it like this:

Cardinality: How Many to How Many

Every relationship can be described by how many rows on each side can be linked. This is called cardinality.

There are three main patterns:

Relationship typeMeaning (simplified)Example
One to oneEach row connects to at most one row on the other sideUser profile, passport
One to manyOne row connects to many rows on the other sideUser and posts, category and products
Many to manyMany rows connect to many rows on the other sideStudents and courses, posts and tags

You will see how to implement each one in the next chapters, but here is the key idea:

Direction: Parent and Child

For a relationship between two tables, it often helps to think in terms of:

Example:

You might hear:

Both describe the same relationship, just from different sides.

How Relationships Are Represented in SQL

Foreign Keys as Links

The most common way to represent a relationship in SQL is:

  1. One table has a primary key, for example users.id.
  2. Another table has a column with the same data type, for example orders.user_id.
  3. You declare that orders.user_id is a foreign key referencing users.id.

In SQL this often looks like:

sql 
CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total   NUMERIC(10, 2) NOT NULL,
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
);

You now have a relationship:

Joins: Reading Related Data

Relationships let you query related data using JOINs (covered later in the SQL chapter). For now, just understand the idea.

Example: Get all orders with their user names.

sql 
SELECT
    orders.id,
    users.name,
    orders.total
FROM orders
JOIN users ON orders.user_id = users.id;

The relationship makes it possible to:

Relationship Constraints and Integrity

Relationships are not just for convenience. They also help enforce referential integrity, which means:

Databases give you options for what happens when a parent row is updated or deleted:

OptionMeaning
ON DELETE RESTRICTPrevent delete if child rows exist (default in many databases).
ON DELETE CASCADEAutomatically delete child rows when the parent is deleted.
ON DELETE SET NULLSet the foreign key to NULL when the parent is deleted (if column allows NULL).
ON UPDATE CASCADEUpdate foreign keys when parent key changes (less common when using surrogate keys).

Important rule: Always choose explicit ON DELETE behavior for foreign keys.
Never rely on "default" behavior without knowing what it is.

Example:

sql 
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total   NUMERIC(10, 2) NOT NULL,
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE RESTRICT
);

Here you cannot delete a user if they have orders. This can be useful when you must keep order history.

Common Relationship Scenarios

Here are some typical things you model in backends, and how relationships help.

Users and Content

Now you can:

Catalogs and Categories

You can:

Memberships, Tags, and Many Connections

This "connector" or "join" table holds pairs of foreign keys. Each row represents one link, for example "User 5 is in Team 3".

You will see the exact pattern of this in the many to many chapter, but it is helpful to already understand the idea.

Practical Design Tips for Relationships

Choose Clear Names

Use names that make it obvious what a foreign key points to.

Bad:

Better:

Even better if there are multiple relationships to the same table:

One Direction in the Database, Both Directions in Code

In SQL, the relationship is stored in one direction:

But in application code you usually think in both directions:

Object relational mappers (ORMs) like SQLAlchemy will let you navigate both ways easily, even though the database only stores one direction.

Avoid Duplicating Relationship Information

If you already have a relationship, do not store the same information twice. That leads to confusion.

Example:

Tables:

Do not add department_name to employees. You can always get it from departments using a join. If you copy it, it can get out of sync when names change.

Think About Deletion Rules

When you add a relationship, always ask:

Some common patterns:

Relationship kindTypical delete rule
Users and login sessionsDelete sessions when user is deleted (CASCADE).
Users and orders (history)Do not allow deleting user if orders exist (RESTRICT).
Optional profile detailsSet profile foreign key to NULL if details are deleted (SET NULL).

Document your choices so that your team knows what to expect.

Examples of Relationship Design

Example 1: Simple Blog

You want to model:

Requirements:

A possible schema:

sql 
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL
);
CREATE TABLE posts (
    id       SERIAL PRIMARY KEY,
    user_id  INT NOT NULL,
    title    TEXT NOT NULL,
    content  TEXT NOT NULL,
    CONSTRAINT fk_posts_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);
CREATE TABLE comments (
    id       SERIAL PRIMARY KEY,
    post_id  INT NOT NULL,
    user_id  INT NOT NULL,
    text     TEXT NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_comments_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

What you can do now:

sql 
  SELECT * FROM posts WHERE user_id = 123;
sql 
  SELECT * FROM comments WHERE post_id = 456;
sql 
  SELECT comments.text, users.name
  FROM comments
  JOIN users ON comments.user_id = users.id
  WHERE comments.post_id = 456;

The relationships make these queries straightforward.

Example 2: Orders and Products

You want to model:

This is a many to many relationship, so you use a link table:

sql 
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE RESTRICT
);

Relationships:

Now you can:

How Relationships Impact Backend Code

Although this chapter focuses on databases, relationships strongly affect your backend code.

Relationships influence:

Key design rule:
Design relationships to reflect your real business rules.
Do not pick a relationship type just because it is easier to code.

Summary

In the next chapters you will look at each relationship type in detail and learn specific patterns to implement them in your schemas.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3039 / 24h
Impressum / Imprint
Datenschutz / Data Policy