Comments/Suggestions on database design - Warehouse Stock Management

Question

I'm currently creating a stock management system that uses multiple warehouses (with sub locations) and since this is my first big project I would love some feedback.

Let me show you what I have done so far... Link as Im still new here

You first need to create a Warehouse, then you can create a Location within that Warehouse.

You can also create an ItemType (ItemGroup), then you can create an Item for that group.

Once you have an Item and Location you can add Stock, the Stock table has a composite key so that duplicates cant be added. I also added a constraint so that you could not enter an Item of the wrong ItemType, same constraint on the Warehouses/Location.

I then need to keep records of each piece of stock, SerialisedItems and NonSerialisedItems. Example: If non serialised stock is added with a quantity of 10 then I currently create 10 rows inside the NonSerialisedItems table (1) that are set to ‘in stock’ with the relevant stock information. If they change the amount of stock then rows would be deleted or added (2).

I could also do with a Van table somewhere that is similar to Warehouse, but think I would have to change the Warehouse table to something like Storage that references two tables, Warehouse and Van?

(1) I currently have a TransactionScope on my page adding x number of rows, Is this the best way to handle that? (2) The Quantity amount in the Stock table would have to count the number of rows for that item and then update the Quantity each time stock is added or removed, any problems here? - Both Questions Fixed - Only create rows for serialised items.

Any other problems?

Well that’s what I have done, if its good or terrible let me know. Also if there are any pitfalls I should be looking out for that would also be great to know.

Thanks

[EDIT] Thanks to Neville K I have made a few changes...

Link to new and improved database

This seems to make a lot more sense! Think I had been staring at it to long yesterday!

(2) is definitely a bad idea. There might be items with large quantities, e.g. small screws. Even worse, you might have items like cables where the quantity can have fractions (310.25m) — Erich Kitzmueller
The large quantities is what I was worried about, I guess I can just create them as needed? And thanks I hadnt thought of cable, if cable is sold in meters I would then be able to store each meter as an individual item so it could still work, or does that feel cheap? — Luckyl337
Cables could also be sold in fractions, like 1/4m. Of course you could create a row for each cm or mm, but it doesn't make sense. I've written several warehouse management systems, and I never felt the need to create a row for each piece of stock unless I have to track a serial number or something similar. — Erich Kitzmueller
Serialised items have unique serials and are tracked, that makes sense to me too but the client would like to keep track of each non serialised items for 'value at sale', 'receipt number'. I also need to be able to place the item into a 'Pick Batch' to move between warehouses. BUT just now I think you have just made me realise... I will be recording sales/purchasing information anyway so why would I need to record this information on each item, lol. As for the temporary ‘Pick Batch’ should I deduct the Stock Quantity and place the items in a PickBatch table until the items are moved? Thanks! — Luckyl337
Immediately deducting stock quantity for the pick batch is IMO not a good idea. Better have two columns in the stock item table, one for the available stock (this one is immediately deducted when a picking list is created) and one for the real stock (deducted immediately after picking). Keeping track of the real (physical) stock situation is an important aspect in warehouse management. — Erich Kitzmueller

Neville Kuyt Neville Kuyt · Accepted Answer · 2012-02-07T13:14:27

Firstly, this is pretty much a solved problem - the best resource I know of is the "data model resource book" series; there's a very flexible model for stock maintenance apps in there.

Secondly, your design is not very normalized, and relies on a lot of duplication. Not sure what the reasoning is, but usually, the "stock" table would have a link to "item", but not "itemType" - the fact that an item belongs to an item type is already captured in the relationship between item and item type, and you don't need to duplicate it. The same goes for location and warehouse.

The key change I'd suggest is the concept of a stock transaction, rather than a single "stock" table.

Something along the lines of

TransactionID      date    itemID  locationID  quantity 
------------------------------------------------------------------
1                1/1/12        1            1       10
2                1/2/12        1            1       -1
3                1/3/12        1            1       20

To find out the current stock for an item, you sum(quantity) group by item; to find the current stock for an item broken down by location, you sum(quantity) group by item, location.

On the first of Jan, there were 10 items in stock; on the second, 1 item was removed, leaving a stock of 9; on the 3rd, 20 new items came into stock, giving a balance of 29.

This design allows you to track change over time, which is a common requirement; it also provides an audit trail by creating transaction meta data (operatorID, invoice number etc.).

Comments/Suggestions on database design - Warehouse Stock Management

1 Answers