Recipe Table

Hi I've got a nice design problem which I'm hoping someone can give me some advice on...

My data relates to the sales for a restaurant chain. I have a star schema in my DWH with a factSales (containing restaurant orders) and a factMenuItems (containing all the products on the menu) linked by a surrogate MenuItemKey field.

Now I have been given a new data source to bring into the mix - a recipe breakdown. This data shows how each menu item is broken down into the constituent ingredients. But it's not a simple 1:n relationship here - some menu items contain what are called 'batch ingredients' which are themselves a recipe in this recipe breakdown table. For example:

Recipe ID	Recipe Name	Qty	Unit	Ingredient
1234	Tomato Ravioli	100	g	Ravioli
1234	Tomato Ravioli	250	g	Tinned Tomatoes
5678	Ravioli	20	g	Flour
5678	Ravioli	1	item	Egg

Notice that the 'Ravioli' ingredient in Recipe 1234 is itself a recipe with ID 5678. The 'Tomato Ravioli' will relate back to the dimMenuItems. The real data has more detail/business keys etc, but this illustrates the problem.

I'm struggling to work out the best design to put this into my existing DWH schema. Should I have an ingredients dimension? Do I need a design with a link table?

Has anybody come across a design like this before? How did you deal with it within the rules of good DWH design?

Thanks,

Nick

It's no different than any manufacturer. You have sales orders for products you make. Products have a bill of materials (recipe). The recipe is a bridge table between products and raw materials.

Thanks for the reply but I'm not clear on how to deal with these 'batch items' - they are ingredients, but are also themselves recipes. So where do I store them? In the dimIngredients table?

Just have one dimension for everything. How it is used is a role.

Forgive me I'm not sure what you mean by a role? And how do I deal with the required self joining?

Think of the menu as a ragged hierarchy of ingredients. (i.e. numerous parent-child relationships).

The bridge table contains all the combinations that make up the hierarchy.

The grain of your fact table and the type of analysis will dictate how you join to the bridge table.

Take a look at :

http://www.kimballgroup.com/1998/09/02/help-for-hierarchies/

Hi!
You didn’t clarify the analysis purposes in your DW, therefore first I suggest a model ( normalized for storing the ingredients of a recipe), than a solution for the dimensional model based on the first model.

1. Normalized model:
Ingredient table
1 Flour
2 Egg
3 Tinned Tomatoes

Recipe table
1234 Tomato Ravioli
5678 Ravioli

Ingredients of Recipes table is an intersection table: each row refers to either an ingredient row or to a recipe row:
Recipe id Ingredient id Ingr.Recipe id Qty Unit
1234     3                 NULL       250 g
1234        NULL           5678         100 g
5678         1               NULL           20 g
5678         2               NULL                   1 item

2. Dimensional model: I suppose you want to analyze from the ingredients. You have to generate a simple bridge table on the basis of the previous intersection table ( ingredients of recipes). The only difference is that you substitute the recipe ingredients with the raw ingredients ( maybe they are nested on several levels). The quantities may vary ( we don’t know at the moment what is  the weight of a portion ravioli…)

Recipe id Ingredient id Qty Unit
1234 3 250 g
1234 1 20g
1234 2 1 item
5678 1 20 g
5678 2 1 item