WMS Database Design (Addressing structure)-1.part

A Warehouse Management System(WMS) could be a stand alone system or it can be integrated with other enterprise software like ERP or even be a part of it. What makes WMS strong and reliable is its database structure. A way data collected, stored and presented could bring significant value not only to logistics but also to the whole enterprise.

I had a great chance to design the database structure of the WMS system. The main aim was to make it flexible as possible, extendable and reliable. Building the WMS, company product structure, company future business development strategy had to be taken into account. I decided to store the data in MS Access. The reason why I chose Access: I was familiar with MS Access and according to company business development plan MS Access was enough to meet logistics needs for at least 5 years. Also it is easy to migrate from MS Access to any SQL servers like MS SQL or even to MySQL which is license free and compatible with Apache server and PHP internet programming tools which are also license free.

What operations should WMS carry on? First and most important process was inbound. For inbound the stock movement possibilities had to be analyzed. Stock is stored by a single unit and moves by a single unit within the warehouse and to outside of warehouse. If the company was a manufacturer of consumer goods, the database structure would be different because the stock movement might be in kilograms, meters, m3 for raw materials. In manufacturing companies stock may come to warehouse in rulons and ordered by meters, kilograms, etc. The main stock movement for an end product of textile goods is as below:
(A) stock movement is a movement of flat units into a container of any type. It could be a carton or a plastic box. (B) movement is a movement of units into a pallet and the last (C) movement is movement of units to the container which is on the pallet. These are all possible movements of the stock during the inbound process for flat products. For units on hangers(GOH) inbound operations are almost the same. For (A) and (B) we need only one addressing, but for (C) movement we will need 2 addressing database structure. It is like: the building number on the particular street and the flat number in that building.

Inside warehouse movements listed below:
(AB) movement: unit from container into static storage(in this case to shelves)

UPDATE ADDRESS SET ADDRESS.PRIMARYADDRESS=NULL, ADDRESS.SECONDARYADDRESS='shelve number' WHERE ADDRESS.PRIMARYADDRESS='container number'

if only one item from container is to be moved into shelve use UPDATE TOP statement. If container consists of mix SKUs add the 'SKU='item SKU'' statement into the WHERE.

(AC) movement: all units in container into another container located in shelve

UPDATE ADDRESS SET ADDRESS.PRIMARYADDRESS='container on the shelve', ADDRESS.SECONDARYADDRESS='shelve number' WHERE ADDRESS.PRIMARYADDRESS='container number'

(AD) movement: whole container into shelve location

UPDATE ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number' WHERE ADDRESS.PRIMARYADDRESS='container number'

(AE) movement: unit from container into a container located in the shelve

UPDATE TOP(1) ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number' WHERE ADDRESS.PRIMARYADDRESS='container number'

(B) unit from a pallet into a shelve(this type of movement is useful for cosmetics items)

UPDATE TOP(1) ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number', ADDRESS.PRIMARYADDRESS=NULL WHERE ADDRESS.SECONDARYADDRESS='pallet number'

(B) unit from a pallet into a container in the shelve

UPDATE TOP(1) ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number', ADDRESS.PRIMARYADDRESS='container number on the shelve' WHERE ADDRESS.SECONDARYADDRESS='pallet number'

(CA) unit from a container located in the pallet into a shelve

UPDATE TOP(1) ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number', ADDRESS.PRIMARYADDRESS=NULL WHERE ADDRESS.PRIMARYADDRESS='container number'

No need to add secondary address into WHERE statement because primary address is unique for the whole warehouse.

(CB) unit from a container located in the pallet into a container located in the shelve

UPDATE TOP(1) ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number', ADDRESS.PRIMARYADDRESS='container number located in the shelve' WHERE ADDRESS.PRIMARYADDRESS='container number'

(CC) container from a pallet into a shelve

UPDATE ADDRESS SET ADDRESS.SECONDARYADDRESS='shelve number' WHERE ADDRESS.PRIMARYADDRESS='container number'

Whole pallet movements are not listed because no racks for pallets will be used.
So to make whole movements we only need a PRIMARY address and SECONDARY address fields.