I currently have three tables:
product_ingredient (m2m relationship).
A product has many ingredients and nested products through
product_ingredient. The nested products themselves have many ingredients and nested products.
How can I efficiently model these relationships so that a single query can select a product’s ingredients through all nested relationships?
- The entities are read more frequently than written, so read optimized would be preferred.
- The actual structure has foreign keys on the
ingredienttable which are aggregated in different ways.
- The product ingredients should be ordered by their position
int, with respect to its