title: MySQL table relationship and how to query a Key/value table
body: <p>I have the following table structure:</p>
  
  <p>Product (id, name, ...)</p>
  
  <pre><code>+-----+------------+
  | id  | name       |
  +-----+------------+
  |   1 | Product #1 |
  |   2 | Product #2 |
  |   3 | Product #3 |
  |   4 | Product #4 |
  +-----+------------+
  </code></pre>
  
  <p>Attribute (id, title, ...)</p>
  
  <pre><code>+-----+------------+
  | id  | title      |
  +-----+------------+
  |   1 | shape      |
  |   2 | colour     |
  |   3 | height     |
  |   4 | weight     |
  +-----+------------+
  </code></pre>
  
  <p>Option (id, title ... )</p>
  
  <pre><code>+-----+------------+
  | id  | title      |
  +-----+------------+
  |   1 | round      |
  |   2 | square     |
  |   3 | oval       |
  |   4 | red        |
  |   5 | blue       |
  |   6 | green      |
  |   7 | tall       |
  |   8 | short      |
  |   9 | heavy      |
  |  10 | light      |
  +-----+------------+
  </code></pre>
  
  <p>and a fourth one (ProductAttribute - id, product_id, attribute_id, option_id), hoping to get "all the red round products which are also tall and heavy":</p>
  
  <pre><code>+-----+------------+--------------------+
  | id  | product    | attribute | option |
  +-----+------------+--------------------+
  |   1 | Product #1 | shape     | round  |
  |   2 | Product #2 | shape     | oval   |
  |   3 | Product #3 | shape     | round  |
  |   4 | Product #4 | shape     | square |
  |   5 | Product #1 | color     | green  |
  |   6 | Product #2 | color     | red    |
  |   7 | Product #3 | height    | tall   |
  |   8 | Product #4 | height    | short  |
  |   9 | Product #2 | weight    | heavy  |
  |  10 | Product #1 | weight    | light  |
  +-----+------------+--------------------+
  </code></pre>
  
  <p>I'm by far not an sql master and maybe my idea can't work.</p>
  
  <p>Edit: </p>
  
  <p>Q1. The question is how do I achieve that? Getting all the red, tall, heavy products for instance. </p>
  
  <p>The following queries don't achieve my purpose:</p>
  
  <p>1:</p>
  
  <pre><code>SELECT ProductAttributes.product_id, ProductAttributes.id FROM ProductAttributes 
  WHERE (ProductAttributes.attribute_id = 1 AND ProductAttributes.option_id = 1) 
  AND (ProductAttributes.attribute_id = 3 AND ProductAttributes.option_id = 4);
  </code></pre>
  
  <p>2:</p>
  
  <pre><code>SELECT DISTINCT ProductAttributes.product_id, ProductAttributes.id FROM ProductAttributes 
  WHERE (ProductAttributes.attribute_id = 1 AND ProductAttributes.option_id = 1) 
  OR (ProductAttributes.attribute_id = 3 AND ProductAttributes.option_id = 4);
  </code></pre>
  
  <p>Note: I'm purposely putting 2 variable in my query, as the real one has many more.</p>