title: Database Table Design : group by in the same table or create child tables and join
body: <p>I am struggling that which is a better design.<br>
  There are 3 tables.<br>
  pr and pr_item and item<br>
  pr => id, name<br>
  item => id, name<br>
  pr_item => <code>prId</code>, itemId, itemPrice  </p>
  
  <p>pr stands for purchase request.<br>
  I'm going to make changes where the users can select multiple suppliers for the PR.<br>
  The supplier is optional though.<br>
  If there's supplier(s) selected for the PR, front end web will display the items group by suppliers. (Each supplier will have their own itemPrice)  </p>
  
  <p>Supplier 1<br>
  Item1 50<br>
  Item2 100  </p>
  
  <p>Supplier 2<br>
  Item1 30<br>
  Item3 40<br>
  Item4 80  </p>
  
  <p>If there's no supplier selected,<br>
  Item1 50<br>
  Item2 100<br>
  Item3 20<br>
  Item4 50  </p>
  
  <p>I've got 2 approaches<br>
  Approach 1 :<br>
  Add one more nullable column called supplierId into pr_item and group by supplierId while retrieving records from pr_item.  </p>
  
  <p>Approach 2 :<br>
  Add 2 more child tables<br>
  pr_supplier -> prId, supplierId<br>
  pr_supplier_item -> prSupplierId, itemId, itemPrice<br>
  So it will be join queries instead of group by.  </p>
  
  <p>Which one is a better approach?  </p>
  
  <p>I'm using MySQL.  </p>