Relationships in SQL – One-to-One, One-to-Many, Many-to-Many Explained

Relationships in SQL – One-to-One, One-to-Many, Many-to-Many Explained  

How to Think About It (From Noob Brute Force to Proper Approach)

Hey, I’m Gaurav.

When I first saw questions like “Show all users with their orders” or “Find products that have been ordered by multiple customers”, I had no idea how to approach them.

My brute-force brain would do this:

Noob Way (What most beginners do):

- Try to put everything in one single table

- Repeat customer name, email, product name in every row

- Write very long and messy queries

- Get confused when data changes (e.g., customer changes email → have to update 50 rows)

This approach feels easy at first but becomes a nightmare later.

Today I’ll show you exactly how I learned to think about relationships in SQL — from that confused brute-force stage to a clean, professional way.

We’ll use the same simple database you can practice on: [sqlpractice.in](https://sqlpractice.in)

Step 1: Why Do We Even Need Relationships?

Noob thinking:  

“Why can’t I just keep everything in one table? It’s simpler.”

Better thinking process:

- Ask yourself: “What data is repeating again and again?”

- If the same customer name and email is repeating in every order row → that’s a red flag.

- Repeating data = storage waste, update problems, and bugs.

Realisation:  

We split data into separate tables so that each piece of information is stored only once.

Step 2: The Three Types of Relationships (How to Think About Them)

Instead of memorizing definitions, ask yourself these two simple questions every time:

1. Can one record in Table A be connected to many records in Table B?

2. Can one record in Table B be connected to many records in Table A?

Based on the answer, you get one of three types.

1. One-to-One (1:1)

Noob way:  

I put all user details in one table (name, email, bio, profile picture).

Better thinking:

- Bio and profile picture are optional and can be very large.

- Not every user will fill them.

- So I should separate them.

Correct approach:

- `users` table → id, name, email

- `user_profiles` table → user_id (FK), bio, profile_picture

Practice Question 1 (Easy)  

Show user name and bio for users who have a profile.  

[Try it live →](https://sqlpractice.in/ )

2. One-to-Many (1:N) – Most Common

Noob way:  

Put customer name and email inside every order row.

Better thinking:

- One customer can place many orders.

- But each order belongs to only one customer.

- So customer details should be stored only once.

Correct approach:

- `users` table (id, name, email)

- `orders` table (id, user_id → foreign key to users.id, amount, ordered_at)

Practice Question 2 (Easy)

Show customer name and all their order amounts.  

[Try it live →](https://sqlpractice.in/ )

Practice Question 3 (Medium)

Find customers who have placed more than 3 orders.  

[Try it live →](https://sqlpractice.in/ )

3. Many-to-Many (M:N)

Noob way:  

I try to put multiple product_ids in one column inside orders table (bad idea).

Better thinking:

- One order can have many products.

- One product can be in many orders.

- Neither table can hold the other’s ID directly.

Correct approach:

Create a junction table (bridge table) called `order_items`:

```sql

CREATE TABLE order_items (

    order_id BIGINT REFERENCES orders(id),

    product_id BIGINT REFERENCES products(id),

    quantity INT,

    PRIMARY KEY (order_id, product_id)

);

```

Practice Question 4 (Medium)

Show all orders with product names and quantities.  

[Try it live →](https://sqlpractice.in/ )

Practice Question 5 (Hard) 

Find products that have been ordered by more than 5 different customers.

How to Approach Any Relationship Question (The Thinking Framework I Use)

1. Identify the two entities (User & Order, Product & Order, etc.)

2. Ask: Can one entity have many of the other?

3. Decide the type (1:1, 1:N, or M:N)

4. If M:N → create junction table

5. Always add foreign key constraints with proper `ON DELETE` behaviour

6. Think about real-world operations: What happens when a user is deleted? (CASCADE vs SET NULL)

Final Thoughts

I used to think relationships were just “some technical thing”.  

Now I see them as the foundation of clean database design.

The moment you start thinking in terms of “one-to-many” or “many-to-many”, your queries become much cleaner and your database much more maintainable.

Go practice all these relationship questions on [sqlpractice.in](https://sqlpractice.in) — the live editor will show you instantly whether your thinking is correct.

Next article (Day 2): Keys in SQL – Primary, Foreign, Unique & Composite

See you tomorrow!