self join table or multikey or ... ?

[GregO]

Can anyone help me on the road?

I have 3 tables:

ingredients products invoice items

Product_ID----=-------ID-----=-----Product_ID

fields of ingredients

name

Quantity/product

fields of invoices

Quantity products

1 invoice can consist of several products.

1 product consists of several ingredients.

1 ingredient can avoid in several products.

1 product can be 'ordered' by several invoices.

I can see how much of each ingredient is needed in total for all invoices.

And I can see what ingredients are needed for each single invoice.

What I can not see is how much of each ingredient is required for each single invoice.

Should I fix this with a self join table or multikey? or something els?

The file is an example of (jonathan stark) that i have changed. see attachment.

Thanks.

A helping hand is enough!

Edited November 9, 2009 by Guest

[Matthew F]

I think you need to be careful to separate tables containing invoice data from tables representing the product catalogue. An invoice might employ a script and relationships to look up current recipe data but this should be copied over to the invoice tables rather than simply viewed through a portal or a calculated field. The reason is that your catalogue of recipes might change with time and you don't want this to change the old invoices.

If you are using a script to copy over the recipe data then it can go on to calculate the ingredient totals also.