3 Replies Latest reply on Jul 8, 2013 10:33 AM by philmodjunk

# Create new records in a child table upon entering a new record in a parent table

### Title

Create new records in a child table upon entering a new record in a parent table

### Post

I am a chef and trying to develop a system to allow the costing of a menu item. I have a table "MenuItems" that is linked to a table "Ingredients". There are many ingredients in each menu item. Works fine.

I have a table"CostCard" that is linked to "MenuItems". The "CostCard" table contains info on how many portions of a menu item are ordered on a given day. One "CostCard" record for a individual day.

I have a join table "CostCardLineItems" that is linked to "CostCard" and "MenuItems". I would like to upon establishment of a new record in "CostCard" ,which is linked to "MenuItem",  have  a new record added to the "CostCardLineItems" table for each Ingredient, from the "Ingredients" table ( all menu items will have varying numbers of ingredients) .

The result would be that the "CostCardLineItems" table would have a record for each Ingredient that was needed for a given "CostCard" Menu Item. I can then do math to determine the correct volume needed of each Ingredient given the number of portions of a menu item that is needed on any given day. This will change with each day "CostCard".

Thanks for the help

Chef Nick

• ###### 1. Re: Create new records in a child table upon entering a new record in a parent table

Seems like you have these relationships:

But can't the same ingredient be used in more than one MenuItem? (If so, I'd consider another join table between ingredients and MenuItem.)

And when you build your list of line items from mutliple menu items for the same day, if an ingredient is used in more than one item, do you want it listed only one time in the line items table for that cost card?

• ###### 2. Re: Create new records in a child table upon entering a new record in a parent table

The way that I have this set up is that the Ingredients table has data on the volume of the ingredient required for a specific menu item. So there may be multipe records for chicken broth because it is used in several different menu items in different volumes. Each record is related to a single menu item so that I can manage the math on volumes required.

Currently I have a cost card record per menu item. For example, a record for fried chicken and another record for mashed potatoes. While both may require butter it would be in different quanities and I need to price each menu item seperately.

Thanks for looking into my problem.

• ###### 3. Re: Create new records in a child table upon entering a new record in a parent table

Then I recommend a join table between MenuITem and Ingredients. A field in this join table would record the quantity of that ingredient for that menu item.