0

*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.

  • can bins be moved between shelves? can an item be in more than one bin (eg, if you have too much of something, might it be spread across two bins?) – Joe Jan 7 '14 at 17:36
  • @Joe At present we usually don't place items in more than one location but I don't that should be a limitation. And no bins would stay where they are. If an item has to move, it would be removed from it's current location and added to a new one. – kylie.a Jan 7 '14 at 17:41
  • 1
    then you might want a join table between the item table and the table to track bins. (and it might make sense to call that one 'locations', or at least not call the bins table 'locations'). Join tables will slow down queries, but you won't be able to track items in more than one bin without it or allow duplicate skus in the items table. – Joe Jan 7 '14 at 19:00
  • @Joe Is this what you had in mind: en.wikipedia.org/wiki/Junction_table – kylie.a Jan 7 '14 at 20:28
  • Yes ... it'd then have foreign keys to the tables it's joining. Minimally, it's just foreign keys to the tables it's joining, but it might also have some attributes itself (eg, tracking how many of the item are in a given bin). – Joe Jan 7 '14 at 20:33
1

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

  • We deal in strictly commodity items The only movements I need to track are adding items to a location and removing them. We have a larger inventory system in place but it's quite cumbersome and slow. This system I'm working on would be primarily for items that arrive in our warehouse and go out to customer within a day or two so we need a more lightweight solution. My ideal solution would be to have every item have a location like shelf_bin. A bin will always be on a shelf. They only come off to put things in or take them out and then they go right back in the same place. – kylie.a Jan 8 '14 at 4:27
  • 1
    @IanAuld - 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. – Joel Brown Jan 8 '14 at 13:04
  • Yes what I mean is there will be shelves that all contain a number of bins and inside the bins there will be bin_contents. So to be clear what you are saying is I need a shelves table, a bins table, an items table and then a bin_contents table that is an intersection of an item and a bin and this is where I should keep the qty info. Do I have that correct? – kylie.a Jan 8 '14 at 14:55
  • I updated my question to reflect your previous comment. Update your answer with the info you gave me and I can accept it as an answer. – kylie.a Jan 8 '14 at 18:40
  • @IanAuld - I've expanded my answer as you requested. NOTE: You do not want to use your Shelf_Contents table, unless you mean to allow a bin to sit on two shelves simultaneously! It is enough to have the Shelf_ID on the Bins table. Each bin sits on exactly one shelf but each shelf can hold many bins. – Joel Brown Jan 9 '14 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.