i've been faced with this dilema about basically designing a sql database.
my requirement is for two tables.
first table product.
second table ingredient.
I have a set of ingredients = IDs and text
Similarly with with product = ID and text
heres the problem, product1 = (ingredient1 + 2 + 4 etc..);
each product in the products table is made up of a set of ingredients.
What i want is, when I select any set of ingredients, it should yeild all the products the selected ingredients are in.
how do i go about it?
i designed a basic model
ingredients table and products table are connected via a mapping table.
the mapping will hold all IDs.
product 1 = ingr 1
product 1 = ingr 2
product 1 = ingr 4
thats how it is. But i wana know if there is anny better way of going about this. specially if
1= i want to add another table like ingredients and connect it to products
2= what if I want two similar ingredients two hold one ID? (its not possible .. but.. )