Designing a recipe database that needs to include ingredients as well as sub-recipes
- by VinceL
I am designing a recipe database that needs to be very flexible as it is going to be communicating directly with our back-of-house inventory system. This is what I have so far in regards to the tables:
Recipe: this table will contain the recipe date: the name, steps needed to cook, etc.
Ingredients/Inventory: this is our back of house inventory, so this will have the information about each product that will be used in our recipes.
Recipe Line Item: This is the tricky table, I want to be able link to the ingredients here as well as the quantity needed for the recipe, but I also need to be able to directly include recipes from the recipe table (such as marinara sauce that we make in-house), and that is why I am having trouble figuring out the best way to design this table.
Basically, the recipe line item table needs to be able to link to either the ingredients table or the recipe table depending on which line item is needed and I want to know what would be the most effective way to handle that.
Thank you so much in advance!