Database Design – 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

Related Solutions

Fact table foreign keys null

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.

Sql-server – Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

In my experience, trying to encode multiple data points into a single column always ends up being more trouble than it's worth. Sure, it seems cool and clever to use BITWISE operators, but there are many things that go wrong and it won't always be efficient to test those bits without cumbersome and unintuitive workarounds. It's the same reason we stay away from storing comma-separated lists, JSON strings etc. in a single column - eventually you care about viewing or filtering on those distinct bits which you now have to extract, sometimes expensively.

With the information I have, my vote is for three separate BIT columns. They will still collapse to similar storage patterns as a single column with the three bits on/off, and can be made more efficient individually and across the board in several ways, including:

data compression
sparse columns
filtered indexes (e.g. WHERE allow_returns = 1)

Someone else advocated for three CHAR(1) columns. These do not benefit from storage collapse and also require a check constraint, making them less than ideal in my mind.

Now, my answer might change if you say, "well, what if I might add 15 other attributes in the future?" I certainly don't think it's wise to build the columns this way if they're not relatively static - changing the schema (and therefore all of the code and interfaces to it) for every new or changed attribute is going to be a royal pain. So in that case you might want to consider EAV - where the attributes are not part of the metadata but part of the data. There are a lot of objections to EAV, mostly around performance and the difficulty in enforcing constraints (in this case unlikely to be an issue if all of these attributes are either on or off), but it worked quite well for us at my previous job. You might model it like this:

CREATE TABLE dbo.Attributes
(
  AttributeID TINYINT PRIMARY KEY,
  Name VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.ItemAttributes
(
  ItemID INT NOT NULL 
    FOREIGN KEY REFERENCES dbo.Items(ItemID),
  AttributeID TINYINT NOT NULL 
    FOREIGN KEY REFERENCES dbo.Attributes(AttributeID),
  Status BIT NOT NULL,
  PRIMARY KEY(ItemID, AttributeID)
);

And again, you can have filtered indexes here to make certain queries much more efficient, such as (imagine the AttributeID for "allow returns" is 10):

CREATE INDEX optAllowReturns ON dbo.ItemAttributes(ItemID)
  WHERE AttributeID = 10 AND Status = 1;

If you have certain attributes that are not on/off (for example, three states of manufacture or shipping), you can change the Status column to:

Value TINYINT NOT NULL

This can double as an on/off value for attributes that are represented that way, and as tri- or more-state value for attributes that require more than simple on/off. You can also reflect which type is which in the metadata of the dbo.Attributes table.

Best Answer

Related Solutions

Fact table foreign keys null

Sql-server – Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

Related Question