title: SQL Table with two relations or table with one relation and repeated rows
body: <p>This is an example, the names are fictitious.</p>
  <p>On the one hand, we have suppliers who provide products to shops:</p>
  <p><strong>Suppliers</strong></p>
  <pre><code>id name
  
  1  GreatSupplier
  2  SuperSupplier
  </code></pre>
  <p>On the other hand, we have shops that sell products to consumers:</p>
  <p><strong>Shops</strong></p>
  <pre><code>id name          supplier
  
  1  NiceShop      null
  2  ShopShop      null
  3  Soop          1
  4  CheapShop     1
  5  MyShop        1
  6  Shopping      2 
  </code></pre>
  <p>There are shops that have their own prices like NiceShop or ShopShop, so they don't have suppliers. But there are shops that use the prices set by the supplier like Soop, CheapShop, MyShop or Shopping.</p>
  <p>Then I want to show all the prices of the products that the shops show to their customers. Something like this:</p>
  <pre><code>NiceShop  - Tomate: 1.23      // shop price
  ShopShop  - Tomate: 1.26      // shop price
  Soop      - Tomate: 1.21      // supplier 1 price
  CheapShop - Tomate: 1.21      // supplier 1 price
  MyShop    - Tomate: 1.21      // supplier 1 price
  Shopping  - Tomate: 1.19      // supplier 2 price
  </code></pre>
  <p>Two options come to mind:</p>
  <p><em><strong>Option 1:</strong></em></p>
  <p><strong>Products</strong></p>
  <pre><code>id id_product   id_shop   id_supplier   price
  1  34           1         null         1.23
  2  34           2         null         1.26
  3  34           null      1            1.21
  4  34           null      2            1.19
  </code></pre>
  <p>When displaying prices, if it is a row with <code>id_shop</code> I show it as is, but if it is a row with <code>id_supplier</code> I join the supplier and the shops.</p>
  <p>Here I can't make a unique index between <code>id_product-id_store-id_supplier</code> and things like this could happen:</p>
  <pre><code>id id_product   id_shop   id_supplier   price
  5  34           3         null          1.21 // wrong
  </code></pre>
  <p>This should not happen as shop 3 has supplier 1 and this is already inserted in id 3.</p>
  <p><em><strong>Option 2:</strong></em></p>
  <p>Another option would be:</p>
  <ul>
  <li>If we insert a price from a shop that does not have a supplier, it is inserted as is.</li>
  <li>If we insert a price from a supplier, a join is made between the supplier and the shops and the same price is inserted several times. (same for updates)</li>
  </ul>
  <p><strong>Products</strong></p>
  <pre><code>id id_product   id_shop   price
  
  1  34           1         1.23      // shop price
  2  34           2         1.26      // shop price
  3  34           3         1.21      // supplier 1 price
  4  34           4         1.21      // supplier 1 price
  5  34           5         1.21      // supplier 1 price
  5  34           6         1.19      // supplier 2 price
  </code></pre>
  <p>This option is a bit cleaner and allows me to create a single <code>id_product-id_shop</code> index but I am creating a <strong>lot of records with repeated prices</strong>, in this example it is duplicated 3 times but in my real environment it can be duplicated 50 times, and that translates into several extra gigabytes of database space.</p>
  <p>Is there a better way to do this?</p>