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.
- No Repeating Values
- No Incorrect Values
- 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:
- One to One - (ex: One person has one Passport and one Passport has one Person)
- One to Many (ex: One Person can have many credit cards but many credit cards can only have one person)
- 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.
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.
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.
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!
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!
Top comments (10)
Hi Alex, excellent article! One element you might be able to help me with... what would the relationship look like when you have multiple recipes that feed into a menu item? That is, the recipe for Mash Potato (with ingredients of Potato and Butter) is used in multiple menu items - eg Steak and Mash (where 80grams of Mash is used), and Sausages & Mash (where 100grams of mash is used)... in each case the Qty of Mash Potato is different. Your assistance would be greatly appreciated. David
Since you want a variable quantity of a recipe, you need to first make a presumption that every recipe sums to some quantity like 100 gm. Then while querying for recipes with a different quantity, you scale the ingredients accordingly. For instance, if 2.5 cups of Yeast yields 100 gms of Pizza, 80 gms of Pizza needs 2 cups of Yeast.
In order to achieve this, you can create a "Menu Table" with columns "Menu ID" and "Menu Description". You can have another "Menu Recipe Table" with columns "Menu ID", "Recipe ID" and "Scale Factor ID". Finally you create another table "Scale Factor Table" that maps IDs to scale factors like 0.8(80 gms), 1.2(120 gms), 100(10 Kgs) etc
Now, to answer a query that asks "What ingredients(along with their quantities) are required to make Steak and Mash?", you go into the "Menu Table" and get the ID of the "Steak and Mash" menu item. You use this ID and index into(join) "Menu Recipe Table" and get the Recipe ID of "Mash". Then you index into "Recipe Table" to get all ingredients of Mash. Once you get the ingredient quantities, you scale them based on the scale factor found in the "Menu Recipe Table".
Hope you get the idea.
Thanks for this! I am fresh out of my first database class at age 42. I have wanted to do this for 22 years and finally took the first step. My first personal project out of the class will be creating a recipe database and your article was the first I came across that was helpful. I have started my ERD. My next project will be to create a database to connect all of my piano sheet music with the digital songs I own so that I can organize a list of songs I still need to buy/learn.
Chico, are you there? Would you reply the following question? Gracias chico!
Thanks a lot for this wonderful article. Just wondering one point: Should we really use a separate entity for Measurement Quantity? I think we should use a quantity field in the bridge table as we only keep numbers in it. What do you think?
Thanks for this Alex. It was informative however I found when trying to build the database following your template gave me more questions.
I have the basic tables set out as you have shown. I have entered the basic ingredients into the ingredients table. I am in the process of entering the quantities into the measurement_qty table and can't help feeling as though I am going down the wrong road as the measurements units table, so far, is redundant.
Thanks for any help/assistance or pointers you can give.
So after reading theabove several times I have entered the cups/packet etc into the measurement_units table and the qty_amount, 1, into the measurement quantity table but still feel I am missing something to tie all the tables together.
I have PK and FK setup but not 100% sure on whether I have done it correctly.
Thanks a lot for this wonderful article. Just wondering one point: Should we really use a separate entity for Measurement Quantity? I think we should use a quantity field in the bridge table as we only keep numbers in it. What do you think?
Very helpful thank you, still have to research more to actually implement these concepts in my app, but this definitely helped me draw an image of it in my head. Much appreciated!
Fantastic article mate, helped improve my understanding of database design from what I was taught at university 😊
I find this helpful, trying to make a recipe app for myself too, thank you!