I am designing the tables for the products module. One issue is ingredients. I want to filter products by ingredients. So to do this then I guess each ingredient must be stored in the DB separately and given an ID also? But lets take a supplement. It may have 50 ingredients. So if there are 1 million products to store and each has 50 ingredients, what would be the best way to store this (relationships)? Like Vitamin E, VitaminB12, etc will each be a separate ID i assume because I want users to find only products that have vitamin E in it, ofcourse there are other filers too like brand name, product name, product category, product type, etc... Plus I am adding social features too so you can find other people who use a product with vitamin E in it. This is a user content social website. Products is only part of a much larger system. The parent of product is a Brand which itself has many many branches. And parent of that is company which again has it's own set of branches.
Thanks.
EDIT But that is the question -> If I normalize it still there will be tons of rows per ingredient entered for each product. How else can we store a huge amount of data per item except line by line? Like in normal code we can use an array and throw it all in but I am not sure in the DB how to fit it all into some structure. My reason is 50 is only an example. Many products have hundreds if you really go in depth into the raw ingredients from the exact chemicals being used. And this is going to be a world catalog of products so expect millions of products. DB is going to be super huge then to add in hundreds of rows per product just to store each ingredient, hmmm... there must be some other way while using a relationship DB? I can store the text in files but I need DB because 1) multi-language support is required and 2) analytic on a per product and per ingredient level filtering up to users.