Relational Relationships Overview¶

A student‑friendly guide to one‑to‑many and many‑to‑many relationships in your database

Understanding Relationships in a Relational Database¶

Relational databases store information in tables, and the connections between those tables are called relationships.

There are two main types of relationships you will see in this project:

• one‑to‑many
• many‑to‑many

Even though they look different in an ERD, they are both built using the same building block:

A foreign key¶

A foreign key is a column in one table that points to the primary key of another table.

This is the foundation of relational modelling.

One‑to‑Many Relationships¶

A one‑to‑many relationship means:

A single row in one table is linked to many rows in another table.

Examples from this project:

• One Role → many Users
• One ItemType → many Items

These are stored directly using a foreign key.

erDiagram
    ROLE ||--o{ USER : "one role has many users"

This is the simplest and most common relationship in relational databases.

Many‑to‑Many Relationships¶

A many‑to‑many relationship means:

Rows in one table can be linked to many rows in another table, and vice versa.

Examples from this project:

• Items can have many creators
• Creators can be linked to many items
• Items can belong to many categories
• Categories can contain many items

Important¶

A relational database cannot store a many‑to‑many relationship directly.

Instead, it uses a bridge table (also called an association table or join table).

How Many‑to‑Many Really Works¶

A many‑to‑many relationship is actually two one‑to‑many relationships with a table in the middle.

Example:

erDiagram
    ITEM ||--o{ ITEM_CREATOR : "item has many creators"
    CREATOR ||--o{ ITEM_CREATOR : "creator contributed to many items"

The item_creator table contains:

• item_id → points to Item.id
• creator_id → points to Creator.id

Each of these is a one‑to‑many relationship.

Together, they represent a many‑to‑many relationship.

This is exactly how relational databases are designed to work.

Why This Matters for Your Project¶

This pattern gives you:

Clean, normalised data¶

No duplication.
No repeated fields.
No messy spreadsheets.

Powerful queries¶

You can search by:

• creator
• category
• item type
• year
• keyword
• combinations of all of the above

Flexibility¶

You can reuse the same structure for:

• books
• videos
• tasks
• products
• assets
• documents
• research papers

The relationships stay the same — only the meaning changes.

Professional‑grade modelling¶

This is the same pattern used in real systems like:

• library catalogues
• inventory systems
• learning management systems
• media databases
• project management tools

Summary¶

• Relational databases store one‑to‑many relationships directly using foreign keys.
• Many‑to‑many relationships are built using two one‑to‑many relationships with a bridge table.
• Your project uses this pattern correctly for creators and categories.
• This structure is flexible enough for any student project.

If you want to explore further, check out:

• docs/architecture/database-model-overview.md
• docs/architecture/search-system-overview.md