Designing a Relational Database for a Cookbook
Tags: tutorial, beginners, webdev, database
Published: 2019-07-02 14:48:09

---
title: Designing a Relational Database for a Cookbook
published: true
description: 
tags: tutorial, beginners, webdev, database
---

When I'm not programming I'm cooking. I love looking through cookbooks and finding new and interesting recipes. Having browsed through my fair share of books I started to notice a pattern in the types of recipe attributes displayed. I thought it might be a good exercise to design a relational database based on cookbook recipes. 

## Background 

So what makes up a relational database? Relations! In a relational database everything is connected. With all things being connected we could just create one large table with hundreds of attributes types, but we don't. Why is that? Data Integrity! Data integrity is the overall completeness, accuracy and consistency of our data. In order for our database to have Data integrity it needs to have these three features. 

1. No Repeating Values
2. No Incorrect Values 
3. No Broken Relationships between tables

So how can we achieve that? We do so by breaking up the data into multiple, manageable, and unique tables. We then connect these tables through relations. When it comes to relations there are only three types we can use (really two, but we'll get to that). These types are: 

1. One to One - (ex: One person has one Passport and one Passport has one Person)
2. One to Many (ex: One Person can have many credit cards but many credit cards can only have one person)
3. Many to Many (ex: Many classes have many students while many students have many classes) 

That last one seemed hard to read and understand right? It is. Plus it doesn't work. The reason it doesn't work has to do with a concept called Parent-Child relationships. Lets look at the "one-to-many" example and try to figure out who is the parent and who is the child. Out of the Person and the Credit card which can't exist without the other? The credit card! There are no magical limitless credit cards floating around that aren't assigned to someone. So, in this scenario, the parent is the person and the card is the child. Now lets look at the "many-to-many" relationship. Whose the parent and child there? On one case a student can't be a student without attending a class. On the other case is a class really a class without students? Here lies why many-to-many relationships don't work. We have no clear parent. 

We solve this problem by breaking up the "many-to-many" relationship into two "one-to-many" relationships. This way we have a clear parent and child. So if we were to model the students and classes tables we'd have something like this. 

![](https://thepracticaldev.s3.amazonaws.com/i/c4egrmyntrivg3k1qyua.png)

Where the "Student-Classes" table would be a child to both the student table and the class table. 

##DB Design

Okay, so armed with this knowledge let's build out a database for cookbook recipes! Let's take a look at a recipe and pull out the relevant information. 

```
Recipe Title: Simple Cheese Pizza
Recipe Description: A great pizza that's really easy to make. 
Recipe Ingredients:
2.5 cups All Purpose Flour
1 Packet Yeast 
1.5 teaspoons Sugar
0.75 teaspoons salt
2 tablespoons Olive Oil
0.75 cups warm water
1.25 cups cheese
1 cup tomato sauce
```

This seems like enough information for us to start with. Looking at the recipe above what data is truly dependent on a single recipe? I would argue the "Recipe Title" and the "Recipe Description", so let's put that into a table. 


![](https://thepracticaldev.s3.amazonaws.com/i/wjqi3siull0bv4r0a3oy.png)
 
Okay so now let's look at the ingredients section. First, we know that a recipe has many ingredients, but also an ingredient has many recipes. I can use 2.5 cups of flour in another recipe, so there is no need to repeat that value again in my database. We can also argue that I can use 2.5 cups of some other ingredient in my database, like 2.5 cups of brown sugar. So is there a need to continuously repeat 2.5 cups in a database? I'd argue not. 

Let's work with an individual ingredient and expand that to all ingredients for a recipe. When looking at an ingredient we notice some key features, we have: measurement quantity, measurement unit, and ingredient. Below show this with "1 cup tomato sauce".

```
1 = measurement qty
cup = measurement unit
tomato sauce = ingredient
```

We could then create three tables based on these three features. 

![](https://thepracticaldev.s3.amazonaws.com/i/y7p892nh07a48qhmmfh2.png)

Okay, so we have all our information represented, but how do we connect that recipe table with the three ingredient tables. We use a join table! 

![](https://thepracticaldev.s3.amazonaws.com/i/ga0f1mk0u10izyfx4td1.png)

What does this database allow us to do? Well, let's take a look at two pizza recipes. 

```
Recipe Title: Simple Cheese Pizza
Recipe Description: A great pizza that's really easy to make. 
Recipe Ingredients:
2.5 cups All Purpose Flour
1 Packet Walmart Yeast 
1.5 teaspoons Sugar
0.75 teaspoons salt
2 tablespoons Olive Oil
0.75 cups warm water
1.25 cups cheese
1 cup tomato sauce


Recipe Title: Simple Cheese Pizza with Pepperoni
Recipe Description: A great pizza with pepperoni that really easy to make. 
Recipe Ingredients:
2.5 cups All Purpose Flour
1 Packet Walmart Yeast 
1.5 teaspoons Sugar
0.75 teaspoons salt
2 tablespoons Olive Oil
0.75 cups warm water
1.25 cups cheese
1 cup tomato sauce
1 bag pepperoni

```

We know that our recipe title and recipe description are different hence we need to store both those values, but look at the ingredients. Nothing really changes except for the "1 bag pepperoni" at the end. Do we really need to duplicate all those values? Well what would happen if we did? Let's look at "1 Packet Walmart Yeast". If Walmart went out of business odds are Walmart Yeast would cease to exist. So we'd have to find a substitute for that yeast and then change not one, but two records. That doesn't seem that hard, but now imagine that we had 10,000 recipes using Walmart Yeast. That's a lot of changing. With the above database we'd only have to change one table value instead of 10,000! Thank you relations!


## Conclusion 

There is no single right answer to database design, but there are a lot of wrong answers. Database design is equal parts art and science. It requires having a deep understanding of the relationships between all the data you are trying to connect. I have found when I don't focus enough time on proper database design my apps become inflexible and inefficient. At the beginning the concept was tricky for me to grasp, especially modeling many-to-many relationships. I'm curious on your thoughts on database design and if its worth spending a large amount of time on. Let me know!