title: How to avoid many SQL joins with several many-to-many junctions for catalog-like application
body: <p>I have a small catalog for an application utilizing SQLite, maybe at most 200 'core' records, the rest of the tables are for further defining them with shared properties. A boiled down similar scenario:</p>
  <pre><code>========= JEWELRY =========
  jewelry_id INT PK AUTO
  name 
  description
  
  ==== JEWELRY_MATERIAL =====
  jewelry_id INT PK FK
  material_id INT PK FK
  
  ======== MATERIAL ======== 
  material_id INT PK AUTO
  name 
  </code></pre>
  <p>The <code>jewelry_material</code> and <code>material</code> design is repeated for around eight other classifiers. I essentially want to have an administration page that summarizes the items in a list like so, while also allowing filtering by each 'classifier':</p>
  <pre><code>Name                 | Material        | Colors    | Styles            
  Gold Diamond Ring    | Gold, Diamond   | Gold      | Classical, Simple 
  ...
  </code></pre>
  <p>Even with only 30 items so far, and each item having maybe 2-10 classifiers used for each of the junctions, is already beyond slow:</p>
  <pre><code>SELECT jewelry.*, 
      group_concat(distinct material.name), 
      --more of the same as above...
  FROM jewelry
  -- MATERIALS
  LEFT JOIN jewelry_material ON jewelry.jewelry_id = jewelry_material.jewelry_id
  LEFT JOIN material ON jewelry_material.material_id = material.material_id
  -- STYLES
  -- More joins...
  -- COLORS
  -- More joins...
  -- --Repeated--
  </code></pre>
  <p>Since this is a small amount of data (tables like 'materials' would only have 10-20) and the core table sits below 400 I figured no denormalization is needed. Is that true? If not how is this problem tackled typically via SQL?</p>