title: Table structure for inventory management in MySQL
body: <p>I am getting a little stuck with the inventory table design and would appreciate if anyone could guide me with that.</p>
  <p>The inventory is at 3 different locations(warehouses). I want help with how do I avoid creating multiple itemcodes for each warehouse</p>
  <p>The current set up is as follows: -</p>
  <h3>table no 1 - <code>inventory_T</code></h3>
  <ul>
  <li>itemcode(primary key)</li>
  <li>item name</li>
  <li>item group id(foreign key to item group table)</li>
  <li>item category id(foreign key to item category table)</li>
  </ul>
  <h3>table no 2 - <code>item_costs</code></h3>
  <ul>
  <li>itemcode(fk to table 1)</li>
  <li>average cost</li>
  </ul>
  <h3>table no 3 - <code>stock_balance</code></h3>
  <ul>
  <li>itemcode(fk to table 1)</li>
  <li>stock on hand</li>
  </ul>