Database model to partially move quantities from one to another and another location

Question

I am struggling with a proper database model allowing me to partially move product quantities from one to another "location" multiple times and being able to track every single item from its original order similar to an inventory system.

Example (descriptive):

• A product "ABC" is being ordered with a quantity of 10 pieces
• The total of 10 pieces for product "ABC" should be stored into location "X"
• Afterwards, the product "ABC" should partially be moved to location "Y" with 5 pieces
• Once product "ABC" arrives in location "Y", 3 pieces should be moved from location "Y" to "Z"

So, how would the database model look like, when the move of such products needs to be tracked by its locations as well as the origin (the "order").

Example (Database Model):

I quickly draw an example db model for illustration. Link

Important things I am concerning about

• flexible input of product quantities based on an overall existing amount ordered in the system (for a single product) by keeping the database in the 4th normal form
• track back to its original order at any place of a location
• list all products incl. details for a location made by the Movement table

Any ideas or existing database models which partially matches my requirement are very welcome.

Answer 1

I have been working for a wholesaler for many years and the way we do it at the database level is (general concepts and VERY simplified):

• the main focus is on the inventory of products in each distribution center (what you call Locations).
• each client is assigned a distribution center, usually the closest for shipping.

• every time an order is placed by a client, the inventory of the distribution center is deduced of the amount he ordered, packaged and shipped.

• we also order products from manufacturers.

• these orders are triggered by automatic inventory replenishment triggers.
• the triggers detect if the availability of a product is below a certain level.
• there is a complex algorithm for this (like what is more in demand for a season, or a city), but this is out of scope for this question.
• every time these orders are received at a center, the process we call Replenishment is executed.
• Replenishment is to add the new inventory in the database, and place the products at their assigned shelves in the center.

So in summary

There are many other processes as well, like Returns, Back Order management, ... but lets keep to the point.

We also have products movements between distribution centers, like you have. But being "inventory focused", movements are handled like a particular case of sales for the center shipping the products, the other as a particular case of replenishment.

IMHO, I do not suggest you calculate the inventory based on movements. This will bring a lot of calculations for nothing. If you have large orders with many items, building an inventory could be a HUGE calculation.

Another problem created by this is that each unit must be identified, tagged somehow, tracked, calculated, reported on, ...

---

Regardless, here is my attempt at a schema for your problem.

My logic:

• Unit: central item in the schema.

• Everything revolves around it, to define the unit, and track where it is.

• Order: an order is a collection of Units, that have been ordered at the same time.

• Order_has_Unit: links units to Orders. Note that since we are tracking each unit individually, every item must be linked here. 8 tables == 8 unit == 8 lines in Order_has_Unit.

• Unit_has_Movement, Movement and Location are used to keep track of each Unit as it moves in the system.

Scenario, receiving an order:

• create one Unit entry per item in the Order.
• link it to an appropriate Product
• define a Movement from [Location == external] to [Location == "X"]
• link that Movement to each Unit received.

To match this design in reality, EACH Unit must be tagged with something to identify it. The bar code is sufficient to identify the product, but not for identifying a single unit. A new "tag number" will need to be created.

---

You wondered what the query to build the inventory in a location would look like. Here is the login (not the SQL!):

• Find each unit where:
• The most recent Movement has idLocationTo == the Location you want
• These could be counted and grouped by Product, so you have totals.

---

I hope this will give you ideas to keep going, and maybe consider other solutions for your requirements. Best of luck!