postgresql – How can I model products with many-to-many ingredients and nested products?

I currently have three tables: product, ingredient, and product_ingredient (m2m relationship).

Product Ingredient relationship diagram

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 ingredient table which are aggregated in different ways.
  • The product ingredients should be ordered by their position int, with respect to its product_id.