Personal Recipe Box Database
Design a PostgreSQL database for your recipes and answer the everyday question: "What can I cook with what I have?". Recipes and ingredients are linked through a junction table (one recipe has many ingredients, and one ingredient is used in many recipes) with the quantity for each. You'll write queries that list each recipe's ingredients, find recipes you can make completely from the ingredients you have, find recipes where you're missing only one or two things (and which ones), and filter by cuisine and cooking time. You'll use pgAdmin to explore the data visually. It's a great introduction to relational databases: you'll learn many-to-many relationships, joins, GROUP BY with HAVING, and writing queries step by step.
- Estimate
- ~8h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · pgAdmin
Project roadmap
- 01
Design the tables
~1.5hInstall PostgreSQL and pgAdmin. Write schema.sql with recipes (name, cuisine, prep minutes, servings, instructions), ingredients (unique name) and recipe_ingredients (recipe, ingredient, quantity and unit, with a composite primary key so an ingredient isn't listed twice in one recipe), plus a pantry table listing the ingredients you have. Add foreign keys and a check that prep minutes are positive. Done when: the schema loads without errors, and the tables appear in pgAdmin.
- 02
Add sample data
~1.5hWrite seed.sql with about 15 recipes from a few cuisines, 40 ingredients, and the links between them with quantities, plus a pantry with some common ingredients. Make sure some recipes can be made with the pantry, some miss one ingredient and some miss many. Done when: the data loads, and pgAdmin shows the rows.
- 03
List each recipe's ingredients
~1hWrite a query joining the three tables to show every recipe with its ingredients and quantities, ordered by recipe name. Then write a version with one row per recipe, the ingredients combined into one text with string_agg. Done when: each recipe shows its full ingredient list.
- 04
Find recipes you can make
~2hWrite a query that counts, for each recipe, its total ingredients and how many are in the pantry (a LEFT JOIN to pantry plus COUNT), and keeps with HAVING only recipes where both counts are equal. Then write a second query listing recipes missing at most 2 ingredients, with the names of the missing ones. Done when: the results match what you can check by hand.
- 05
Filter by cuisine and time
~1hWrite queries filtering recipes by cuisine and a maximum prep time, and combine them with the can-make query (for example quick Italian recipes I can make now). Save the most useful queries as views. Done when: the filtered queries return the right recipes.
- 06
Test the queries
~1hWrite test.sql that, in a transaction, changes the pantry (adding and removing an ingredient) and checks that the can-make and missing-ingredients views change as expected, raising an error with a DO block if not, then rolls back. Done when: psql -f test.sql runs without errors.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack