Are you able to post your current schema definition? Having that kind of specific information will help.
Meanwhile...
1) Does this mean I need to create a different table for every location (this doesn't seem wise as I don't know how many location will exist as the company grows in the future)?
Your instinct is correct; you don't want multiple tables for locations. How would you write a query in a database like that without having to revise and rebuild your application every time you wanted to add or remove a location?
2) Can I create a data table to "define" the warehouses, the ID numbers, etc.?
Yes, that's better. A table that represents the various locations parts could be stored. Then your inventory can just refer to that table to indicate where a part is.
3) if so, where do I store the dynamic (changing) data for each part number/location for things like selling price, number of units sold, total value sold, etc.?
In general, keep information about an object with the object it's about. Examples:
- Selling price is probably about the part itself, for a simple application.
- Number of units sold is about a specific order.
- Total value sold is about an order. You could store this with the order, but there's no need to actually save this value anywhere--you can always calculate it for any order by adding up the prices.
4) Must I break apart the existing INVENTORY …