Do We Need 5 Tables for the CS50 SQL Donuts Database?

[Rajeev Bagra]

Source: https://cs50.harvard.edu/sql/psets/2/donuts/

Question

While working on the CS50 SQL Donuts problem set, I identified the following tables:

1. ingredients
2. donuts
3. customers
4. orders
5. donut_ingredients (many-to-many relationship between donuts and ingredients)

Is it true that 5 tables are needed?

Answer

Yes, a 5-table design is a reasonable normalized database schema.

The first four tables store the main entities:

• ingredients
• donuts
• customers
• orders

However, there is a many-to-many relationship between donuts and ingredients:

• One donut can contain many ingredients.
• One ingredient can be used in many donuts.

Relational databases cannot directly store many-to-many relationships, so a junction table is needed.

Example

Instead of:

donuts
------
id | name | ingredients

we create:

ingredients
-----------
id | name

donuts
------
id | name

donut_ingredients
-----------------
donut_id | ingredient_id

This allows each donut to be linked to multiple ingredients and each ingredient to be linked to multiple donuts.

Is 5 Tables Always Enough?

Not necessarily.

There is another many-to-many relationship:

• One order can contain many donuts.
• One donut can appear in many orders.

A fully normalized design often introduces a sixth table:

order_items
-----------
order_id | donut_id

The complete schema becomes:

customers
    |
    v
orders

orders <----> order_items <----> donuts

donuts <----> donut_ingredients <----> ingredients

Key Takeaway

A 5-table design is sufficient to model the donut–ingredient relationship.

However, if you want a fully normalized database that also properly models orders containing multiple donuts, a 6-table design using an additional order_items junction table is usually preferred.

[Author]

Posts