I had an idea awhile ago, before learning how to build and maintain databases, to make a website or app to help keep track of items in my pantry. Now that I have the ability to build and maintain databases, I think this idea can become a reality. Ideally, in the end this app will:
- Give us a list of every item in our pantry as well as the quantity remaining
- Generate a shopping list once a week of items that we have run out of or are about to run out of
- Generate a trash list once a week of items that have expired and need to be thrown away
- Usable as a recipe book
- Ability to add recipes for cooking and add the needed items to a shopping list
- Ability to randomly choose recipes, with the option to only allow recipes that we have all of the ingredients for (for those times when you are feeling indecisive)
- Ability to learn/predict the shelf life for things like fresh fruit and meat
- Ability to import recipes from major recipe websites
- Ability to easily update/revise recipes
As always, this is a living list and will more than likely change. This project has the potential to grow really large, so I will try to break it up into small, individual projects.
Since the end goal is potentially an app, I will use SQLite as my RDMS because it is highly portable and compatible with phones. The database will be the foundation for this project, so it will be the thing I work on first.
I found a free and easy to use tool to help make professional looking database schemas. It is called drawio and can be found here. The schema below was created in just a few minutes using this website.
My pantry database will have two main functions:
- track items in my kitchen and house
- track recipes
These could be broken up into two separate databases. However, I want to start small and then expand. I think it would be easier to break these out into separate databases later than it would be to merge them into the same one. Because of that, I decided to start with them together.
The kitchen items tables are simple. The food_items table will hold the food name, quantity remaining (this is mostly a place holder for now — I have big plans to design a system that could automatically track the percent remaining for the food items in your house), expiration date, shelf life, category, and location.
The category table will have two columns: id and name. Right now, I think there will be 9 categories to choose from: spices, meat, bread, dairy, baking, produce, canned, cleaning, house.
Finally, we have the locations table. This table will also only have the id and name columns. It will have the rows: fridge, freezer, pantry, closet, and bathroom.
The next few tables are to keep track of my recipes. There are four tables: recipes, steps, ingredients, and recipeingredients.
The recipes table is easy. It will track the recipe name, description, number of servings, serving size, calories (per serving), total time, active time, and source. Eventually, I might add the ability to rate and/or “favorite” recipes.
The next easiest table is steps. The primary key for the steps table is a combination of the recipe id and step number. The step number will auto increment for each recipe (similar to how a revision might increment). For instance, recipe id 1 can only have one step number 1 and one step number 2. However, recipe id 2 can also have step numbers 1 and 2. Making the primary key a combination of these two numbers allows us to keep track of steps in the recipe!
Finally, we have the ingredients table. Each recipe may have many ingredients and each ingredient may be in many recipes. Because this is a many to many relationship, we have to use a junction table (this is discussed in my post on relationships). The junction table in this case is called recipeingredients. The primary key for this table is a combination of the recipe id and the ingredient id. This table is also where the amount of each ingredient will be kept.
Now that the database is seemingly mostly thought out, the next immediate step will be to build it up in SQLite. After that, I plan on creating a quick GUI in either Visual Basic or Python to allow me to quickly enter and read back the information in the database and try to actually use it. Part of this will hopefully be setting up reminders for when we need to throw out food that is expiring or past its shelf life.
If you have any comments or suggestions, please let me know in the comments or contact me!