狠狠撸

Storage
Management
System
Outlining the schema of an inventory
storage management system
By: Matthew Saragusa
polyhedron@gmail.com

How is a product stored?
? A product should have a lot code, product number, description and
quantity
? This product grouping of a specific quantity should be assignable to a
storage bin / pallet / or box.
? This box should be able to be assigned to a location
? This location should have a physical location of the node
? This location should have definitions of local nodes
? Distance between nodes should be able to be calculated

Product based information
Product Information
? Product number
? Product description
Product Bundles
? Quantity
? Create Date
? Lot code
? LPN

Products are grouped into collections of
products
Say you have a collection of products or goods which are currently
stored in a bin or on a pallet. And let’s say that you want to move that
entire box to a new location. You do not want to have to scan each and
every object located in that bin or on that pallet to the new location!
The better way to do this would be to assign this bin or pallet to an
LPN. This LPN is a reference for the bin or pallet which contains all the
items which you want to move. Think of this as the parent to the
collection of items.

LPN based information
? Create Date
? LPN
? Location

Location Nodes
? These locations have an ID
? Locations can either be physical locations or navigation nodes in the
warehouse. A location can be a physical location on a pallet rack, a
location on a shelf, or an intersection of aisles in a warehouse.
? We will classify these locations a either nodes (for navigation) or
location

Node based information
? Node ID
? Node Name (will typically be the ID)
? Node type (navigation or location)
? Z height – this is the level of the location (0 will be ground)
? Zone – zones are useful when determining whether or not node
based movements are required. When locations are located in the
same zone

Edges
? Edges are the connections between the nodes
? They define the distance between the nodes
? An edge connects only two nodes
? Edges should not be created between one bin location node to
another
? Only edge types should be between bin location node to navigational
node (which would be a corner of an aisle) and navigational node to
navigational node

Edge based information
? Edge ID
? Start node
? End Node
? Edge length – the length of the node is the distance between nodes.
This is a manual measurement of physical length between nodes

Database Design
So far we have described this database design. With this
design, if we know the product number, we can identify
the LPN of the bin or pallet where it is located as well as
the nodes they are attached to. The bins will be located
in nodes with the node type of location. This will either
be a pallet rack location or a floor position.
In addition, these nodes will also have edges attached
to them, which will tell the distance between a
particular node and other nodes with direct access.
When designing the warehouse, it will be important to
notate any connected node. These edges will contain
the distance of a pallet position to a corner of an aisle,
the distance from one corner to another, and a distance
from a corner to a dock door, as well as many other
situations. This will be beneficial when warehouse
routing is used to direct forklifts to the most efficient
product in the warehouse.

Product to LPN Lookup
When selecting a product, all product bundles can be found by the
product ID. The bundle is a group of like products which are together
that have the same lot. Each bundle is associated to an LPN.
A product may have many bundles associated with it, even
with similar lot codes.
Each bundle must be associated with an LPN. The LPN
defines a collection of one or more product bundles.
LPN lookup is as simple as looking for all bundles associated
with the productID and returning the LPNID.
SELECT LPNID FROM ProductS LEFT JOIN ProductBundles
WHERE Products.productNumber = {productNumber} AND
Products.productID = ProductBundles.productID

Product to Location Node Lookup
Similar as before, use the productNumber to get the ID and then use
that to look up the bundle. Once you have the bundle, you can get the
LPNID and then identify the node which it is attached to. In the LPN
insert procedure, there should be restrictions which only allow LPNs to
be linked to location node types only.

Routing
Now if we had a product that needed to go to a location in a warehouse, what we would want to do is first find
the oldest group of product bundles. If there were only one bundle, this would be easy, we would direct the
operator to that bundle to pull and put in the destination location, but if there were multiple bundles, we
would want to find the closest bundle to the destination point. This is why we created node edges. These
edges define the distance between nodes. These edges are set up when the warehouse is created.
In order to find the closest bundle, we would need to take each bundle’s location node and run a routing
algorithm to find the shortest route from that node to the destination node. Then send the operator to the
closest node. In it’s simplest form, an algorithm such as Dijkstra’s algorithm could be used to find the closest
node by using edge lengths between nodes.

狠狠撸

Warehouse Storage Managment System Database Schema

Recommended

More Related Content

What's hot (20)

Similar to Warehouse Storage Managment System Database Schema (7)

Recently uploaded (20)

Warehouse Storage Managment System Database Schema