Recipe Table

View previous topic View next topic Go down

Recipe Table

Post  nirst on Mon Aug 05, 2013 9:30 am

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 IDRecipe NameQtyUnitIngredient
1234Tomato Ravioli100gRavioli
1234Tomato Ravioli250gTinned Tomatoes
5678Ravioli20gFlour
5678Ravioli1itemEgg

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

View user profile

Back to top Go down

Re: Recipe Table

Post  ngalemmo on Mon Aug 05, 2013 10:31 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Recipe Table

Post  nirst on Tue Aug 06, 2013 6:04 am

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

View user profile

Back to top Go down

Re: Recipe Table

Post  ngalemmo on Tue Aug 06, 2013 8:46 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Recipe Table

Post  nirst on Tue Aug 06, 2013 10:44 am

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

View user profile

Back to top Go down

Re: Recipe Table

Post  LAndrews on Tue Aug 06, 2013 4:33 pm

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/


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Recipe Table

Post  gvarga on Sun Aug 11, 2013 9:40 am

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

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: Recipe Table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum