table structure for inventory management in mysql
i am getting a little stuck with the inventory table design and would appreciate if anyone could guide me with that.

the inventory is at 3 different locations(warehouses). i want help with how do i avoid creating multiple itemcodes for each warehouse


the current set up is as follows:-
table no 1 - `inventory_T`
fields-
itemcode(primary key)
item name
item group id(foreign key to item group table)
item category id(foreign key to item category table)

table no 2 - `item_costs`
fields-
itemcode(fk to table 1)
average cost

table no 3 - `stock_balance`
fields-
itemcode(fk to table 1)
stock on hand



table structure for inventory management in mysql
===

i am getting a little stuck with the inventory table design and would appreciate if anyone could guide me with that.

the inventory is at 3 different locations(warehouses). i want help with how do i avoid creating multiple itemcodes for each warehouse


the current set up is as follows:-
table no 1 - `inventory_T`
fields-
itemcode(primary key)
item name
item group id(foreign key to item group table)
item category id(foreign key to item category table)

table no 2 - `item_costs`
fields-
itemcode(fk to table 1)
average cost

table no 3 - `stock_balance`
fields-
itemcode(fk to table 1)
stock on hand


