title: Multiple column vs multiple tables for foreign keys mysql
body: <p>I have a table (products)</p>
  
  <pre><code>id-&gt;PK
  product_name
  </code></pre>
  
  <p>I have another table (ingredients)</p>
  
  <pre><code>id-&gt;PK
  ingredient_name
  </code></pre>
  
  <p>I have a third table (purchase_order)</p>
  
  <pre><code>id-&gt;PK
  </code></pre>
  
  <p>This purchase order can have many lines of either products or ingredients or both.</p>
  
  <p>Now what would be the best approach design-wise? I will be doing all sorts of queries within these tables. I prefer Approach 1 even though there will be many null values but would like to hear your thoughts on this too. These is no option to have a super table which will work as a parent of products and ingredients.</p>
  
  <p><strong>Approach 1:</strong></p>
  
  <p>Creating the table purchase_order_line with the following fields</p>
  
  <pre><code>purchase_order_id
  product_id(nullable references products)
  ingredient_id(nullable references ingredients)
  </code></pre>
  
  <p><strong>Approach 2:</strong></p>
  
  <p>creating 2 tables:</p>
  
  <p>purchase_order_products</p>
  
  <pre><code>purchase_order_id
  product_id(references products)
  </code></pre>
  
  <p>purchase_order_ingredients</p>
  
  <pre><code>purchase_order_id
  ingredient_id(references ingredients)
  </code></pre>