Inventory Management DB Design

database-design

*strong text*I am putting together a small inventory management system and have some questions concerning the database design. What I was planning on doing was to have 3 tables, items, bins and shelves. Items would be created and added to the table. When we have stock of an item it will be in a bin on a shelf. If we don't have stock the item will still be in the items table but it won't have a location or the location will be set to a value that indicates no stock.

I started the items table but I wasn't quite sure how to create the relationship between all the tables. I believe a foreign key is what I would be looking for but I'm not sure on the proper way to implement it. I was thinking the following lay out for the tables but as I started coding it I realized this wouldn't work.

UPDATE: In response to the answer I received this is the query I came up with. It's wrapped in Python so ignore that.

db = 'inventory.sqlite'
(cur, con) = open_db(db)
cur.execute("CREATE TABLE Items (item_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            sku TEXT UNIQUE NOT NULL, \
            title TEXT UNIQUE NOT NULL 
            )")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Bins (bin_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            name TEXT UNIQUE NOT NULL)")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Shelves (shelf_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            name TEXT UNIQUE NOT NULL)")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Bin_Contents (bin_id INTEGER NOT NULL, \
            item_id INTEGER NOT NULL, \
            qty INTEGER NOT NULL, \
            CONSTRAINT PK_Bin_Contents PRIMARY KEY (bin_id, item_id), \
            FOREIGN KEY (bin_id) REFERENCES Bins (bin_id), \
            FOREIGN KEY (item_id) REFERENCES Items (item_id) \
            )")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Shelf_Contents (shelf_id INTEGER NOT NULL, \
            bin_id INTEGER NOT NULL, \
            CONSTRAINT PK_Shelf_Contents PRIMARY KEY (shelf_id, bin_id), \
            FOREIGN KEY (shelf_id) REFERENCES Shelves (shelf_id), \
            FOREIGN KEY (bin_id) REFERENCES Bins (bin_id) \
            )")
close_db(con)

After writing that and looking at it I realized that wouldn't function the way I wanted it to. As I mentioned earlier I believe a foreign key is what I need but I'm not sure about the best way to implement it. I would like to be able to:

UPDATE: Will the above query create the DB/tables i need to do the following:

  • Use the app to search for items and display where they are located
    and how many are in each location.
  • Use a shelf/bin number to pull up the contents of that shelf/bin. For
    example the user selects shelf_a from a menu. All the bins in
    shelf_a are displayed. The user can select a bin and see it's
    contents.
  • Perform basic management tasks. Add an item to a shelf/bin, remove
    some/all the quantity of an item from a shelf/bin, add new
    shelves/bins etc with out anything disappearing. Shelves are still
    there even they are empty same for bins, they are still there even
    when they are empty.

I don't need help with the queries themselves I just need to make sure the proper relationships are in place so when I write the queries the right info comes back.

Best Answer

There are some things you want to think about based on the nature of your items and how you store and track them.

Are your items discrete or are they a commodity? The way you would track television sets, each of which has its own distinct serial number is different from how you might track boxes of nails. If your SKUs are discrete you don't have a "quantity" field, instead you track individual items with an intersection table (as suggested by Joe). Otherwise whether you need an intersection table depends on how many different SKUs can be on a shelf (see below).

Is it important to track inventory movements? Do you need to see stock deliveries and shipments? If so you might want to take a kind of double entry accounting approach, treating shelves like GL numbers. If not, a simpler quantity per shelf may be sufficient.

Can multiple SKUs share a shelf? If so, you need an intersection table. If not, you can get by with a foreign key from Shelf to Item.

Does every bin have at least one shelf? Your application (queries) will be much simpler if every bin has at least one shelf, such that you don't need to track inventory which is in a bin but has no applicable shelf information.


EDIT:

It's important to get the terminology clear. When you say "bin" you mean a box without a lid that sits on a shelf. In a lot of inventory situations, a "bin" is a space on a rack where you could put a skid or one or more shelves.

As long as we're clear, you want a hierarchy of SHELVES each of which has many BINS each of which can have many BIN_CONTENTS each of which is an intersection between BIN and ITEM and which has a quantity. Don't store quantity on your ITEM table, store it on the intersection table, that way you can have multiple bins of the same item, or even many different kinds of items, each in their own distinct bins or even multiple different items mixed within a bin, all on the same shelf.

Consider the following ERD:

ERD