How to manage item locations in an inventory

Question

I have to manage item locations and life cycle as a new requirement. I'm not familiar in warehousing/inventory/storage systems, so maybe my question is trivial.

The task:

We have original items that will be processed multiple times. In case of some processing steps the original item will be divided into multiple sub items.

As storage location we have cabinet, shelf and box. The box can store multiple items, it has rows and columns.

We have to manage not only the current position but the complete location lifecycle even after the item was sold.

Technology: Java, Spring, Hibernate, MySQL

Question: How to manage connection between item and location? I have 2 solution in my mind:

1. Store the location attached to the item: So Item has a boxId, row and col. In this case no dedicated Location object is created.
2. When storage boxes are registered in the system it automatically creates Location objects and the Item is associated to this Location. For example if the admin adds a new 5x5 box to the system it will create 25 Location objects in the background.

As I have no experience in this topic so I'm not sure what hidden pros and cons each solution have. Maybe there are other,better ways to solve this problem.

Update:

To give more details I try to summarize what we know at the moment.

• Only relatively small amount of items will be stored that means 5-10.000/year. At the beginning even less. Very few user (maximum 1-2 in the same time) will use the system and response time is not critical.
• There are multiple size of items, but all of them are stored in boxes.
• Item can be: processed, splited, stored, moved, sold
• Item has a type that can be: ingredient, pre-processed item and finally it will be a product to sell.
• The history must contain the final product complete location history. Not only its own, but its parent items location history as well.
• Of course we need to know the item current location, or the fact that is already sold.
• It's also required to see how many empty location we have, just to know when to buy new boxes, cabinets.
• There are storage requirements like humidity, light, temperature. Depending on the item lifecycle it varies. The cabinet determine these conditions.
• There are also size constraints. The original ingredient item is bigger so a box can hold less item as in case of the final product.
• Cabinets and boxes have their own unique identifiers.
• All stored item - independent from the actual life cycle - has its own unique identifier. This will probably change in the future if new item types without UID will be introduced.
• The storage hierarchy is fix at the moment: Cabinet -> shelf -> box. However this will - almost sure - change in the future if the first version works well. So it can be more and also less levels for other items.

Answer 1

There are a number of unknowns in your description, so either of the two solutions (or a somewhat different one) could be best suited.

If the containers are managed in a hierarchy (an item is at a location in a box, the box is on some level on a shelf etc.) designing data structures around these containment relationships might make sense. If all you have is basically location numbers in boxes, attributes in the item objects might be enough.

Probably it helps if you think about the use cases, especially regarding the item life cycle and history.

• What are the actions performed on items? Create, process, store, retrieve, move, split, join, ...?
• How must these actions be logged in the item history?
• What are the current and foreseeable requirements for the management of containers and item locations, reporting, search, etc.

These are just a few of the questions you should ask yourself and the stakeholders to come up with a good design. Software design is hard work that can't be shortcut by just asking a question on stackexchange.com :-)

Answer 2

With a lot of things defined vaguely, I'll still hazard to suggest a few things.

Where there is a hierarchy (a tree), there are paths. Look at the file system.

If I the number of path steps is fixed, I would just have a tuple of (cabinet_id, shelf_id, box_id, row, col) for each item, and index them in that order. This would allow for efficient range queries, like "all items on that box of this cabinet".

If you have colossal amounts of items, you could have a separate table associating an item with a box, and maybe a box with a shelf. It would make item rows smaller, at the expense of having to do joins for searching. I would not pursue this approach until I have at least a million items, and am visibly short of RAM.

I would not try to think in objects when working with a relational DB. I would create the minimum amount of objects to reflect the hierarchy on the Java side. There is a balance between doing unused queries and fetching unused data (e.g. shelf and cabinet descriptions) and latency to access such data when fetched just-in-time. This takes consideration first, and measurement and analysis next.

You may discover that you don't need objects for boxes or even shelves if no information is associated to them. To future-proof the design, and to make typechecking easier, I would still create classes for them, even if they currently just wrap an identifier.