Recipe Table
4 posters
Page 1 of 1
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:
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
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
nirst- Posts : 3
Join date : 2013-08-05
Re: Recipe Table
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.
Re: Recipe Table
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?
nirst- Posts : 3
Join date : 2013-08-05
Re: Recipe Table
Forgive me I'm not sure what you mean by a role? And how do I deal with the required self joining?
nirst- Posts : 3
Join date : 2013-08-05
Re: Recipe Table
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/
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/
LAndrews- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: Recipe Table
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
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
gvarga- Posts : 43
Join date : 2010-12-15
Similar topics
» How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema
» Transactional detail fact table w/complimentary snapshot table. How do the two play together?
» Dimensional table design dilemma, Aditional column or Xref table
» How to resolve a many to many relationship? Bridge Table or Utilize the same fact table?
» separate fact table/different grain - do I need a bridge table
» Transactional detail fact table w/complimentary snapshot table. How do the two play together?
» Dimensional table design dilemma, Aditional column or Xref table
» How to resolve a many to many relationship? Bridge Table or Utilize the same fact table?
» separate fact table/different grain - do I need a bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum