CODEWITHSUNDEEP
mysqlsql
mmdel
mmdel

Reputation: 1279

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

table no 2 - item_costs

table no 3 - stock_balance

Upvotes: 0

Views: 15278

Answers (2)

Tudor Constantin
Tudor Constantin

Reputation: 26861

I suppose you have a single database which serves all warehouses.

Create another table

warehouses

  • warehouseid (PK)
  • name
  • other details

And then add the field - warehouseid to table stock_balance as a FK(Foreign Key) - this way you will bind each stock of an item to a warehouse

Upvotes: 0

Mel
Mel

Reputation: 6157

First of all, you need to describe your problem:

How can I store the information about items so that I know how many are at each location?

The three things in bold are the key pieces of information. Which means, we need to be able to keep track of locations and items and everything that can be different for a given location. Everything else about the items we store in the items table. Everything else about the location we store in the locations table. The above problem description says only the amount stored is different, but it can be for your problem that other things about the items are different, modify as needed.

I would go as follows:

Table: Items

  • Item ID
  • Item name
  • Item price
  • .... various other interesting properties

Table: Warehouses

  • Warehouse ID
  • Warehouse name
  • .... various other interesting properties

Table: stock

  • Item ID (fk to Items)
  • Warehouse ID (fk to Warehouses)
  • Quantity

Note: Item ID and Warehouse ID combined is the primary key.

Upvotes: 1

Related Questions