Database schema for Kitchen Manager Application

I have been looking for ways to manage recipes, pantry inventory and shopping lists in a more consistent manner. Cookbooks are just gathering dust and meal planning is time consuming. When you think about the problem it comes apart quite easily into distinct entities that would be best tracked in an SQL database with an API of some kind and a React front end application to provide the user experience.

Recipe management applications such as Gourmet Recipe Manager offer tons of functionality such as:

  • saving recipes in a database with images, ingredients, ratings
  • automatic scaling of recipes to feed more or less people
  • generation of shopping lists based on chosen recipes
  • management of inventory and recipe suggestions based on what is stocked

This is pretty nice, however a Windows desktop application without API doesn’t cut it for me. This sort of kitchen management application would benefit hugely from integration into home automation servers such as Home Assistant. I want to be able to have all that but extend it with custom integration to improve the way data is entered or managed.

One idea is to let a camera take images of newly bought groceries, interpret the barcodes and update the inventory database based on the quantity of itmes bought as well as their typical expiry date. Some time later, this would enable notifications for inventory items (such as cooking cream) that are due to expire which will help reduce cost and food waste.

This is yet another monumental idea that I came up. sounds great and I’d love to have it in production… but realistically it is way too time consuming to ever finish by myself.

Not too long ago I attempted to write a budget application with a twist… and am still struggling to complete it. It is usable in a very alpha version kind of way.

So anyway, here is a crude database schema showing how the different entities connect.

DB schema for kitchen manager application

Recipes mOdle


== Recipe
name
rating
instructions
yields
yield_quantity

recipe_ingredients
recipe_id
ingredient_id
quantity
unit_id

== ingredients
id
Unit_id
name
pantry_item : boolean

== unit
id
name

== shopping_list
id
date

== shopping_list_recipes 
shopping_list_id
recipe_id
quantity

== shopping_list_items
quantity
shopping_list_id
ingredient_id

== product
id
ingredient_name
brand
barcode

== inventory
id
product_id
expiry_days