🤔 Why Not Always Put "Relationships" in the Table? The Secret of the Join Table!

Have you ever found yourself struggling to link two tables in a clean way when designing a database?

For example, you have Dishes and Categories, and each dish can belong to multiple categories, and each category can include multiple dishes.

At this point, you might ask yourself:

Should I put the category ID in the dishes table? Or should I put the dish ID in the categories table? Or what exactly should I do? 🤯

This is where the hero comes in: The Join Table!

Let’s explain why it’s the ideal solution, and we’ll provide clear examples and tables to support the idea.

💡 What Does a Many-to-Many Relationship Mean?

A many-to-many relationship means that both sides can have multiple connections.

A simple example:

Pizza appears in multiple categories, and the "Snack" category contains multiple dishes.

❌ Why Can't We Just Put It in One Column?

If you tried storing category IDs as a list in the dishes table like this:

It breaks the normalization rules and makes searching and querying incredibly difficult.

SQL doesn’t work well with string arrays.

✅ The Ideal Solution: The Join Table

The Join Table is a third table that only contains the relationships between two tables.

Imagine this structure:

Dishes Table dishes

Categories Table categories

Join Table dish_categories

🚀 Why is the Join Table Better?

1. Data Organization and Cleanliness

• No duplication or mixing of data within cells.
• Each value is in its own cell, as the rules suggest.

2. Stronger and Simpler Queries

You can easily perform a simple JOIN to get:

• All categories linked to a specific dish.
• All dishes within a specific category.

SELECT dishes.name
FROM dishes
JOIN dish_categories ON dishes.id = dish_categories.dish_id
JOIN categories ON categories.id = dish_categories.category_id
WHERE categories.name = 'Healthy';

3. Flexibility for Future Expansion

If you want to add additional information to the relationship (not to the dish or category itself), like:

• Display order within a category.
• Whether the relationship is active.
• Admin notes.

It’s easy to add columns to the dish_categories table.

🧱 SQL to Create the Tables:

CREATE TABLE dishes (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE dish_categories (
  dish_id INT,
  category_id INT,
  PRIMARY KEY (dish_id, category_id),
  FOREIGN KEY (dish_id) REFERENCES dishes(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

🎯 Conclusion:

The Join Table is not just a "technical fix," it’s an essential part of designing databases in a clean, flexible, and scalable way.

Whenever you encounter complex relationships, always remember that the right solution isn’t always adding a new column… sometimes the right solution is adding a new table 😉