-1

I am developing a Warehouse Management System. Each Warehouse can specify storage locations by varying degrees of specificity: Zone, Aisle, Row, Rack, Shelf, Bin. While Bin is always required because it is the final location, some Warehouses may not need the other location types. For example, some Warehouses may not have racking, so they don't need Rack or Shelf location types. The user can enable/disable location types for each Warehouse.

This is a classic parent-child (one to many) relationship between each storage location. One Zone can have multiple Aisles, each Aisle can have two Rows, each Row can have multiple Racks, etc. However, depending on the settings for the Warehouse, the parent-child relationships may change. Suppose a Warehouse only has Zones and Bins enabled. Then Zone is the parent to Bins, and nothing else in between.

What is the simplest and most effective way to structure these relationships in the database? I thought of two options but I am open to better ideas:

Option 1
Never change the parent-child relationship structure. Always create one of each storage location, but hide in UI if it is disabled for that Warehouse. In this case, there will always be at lease one of every parent. For example, Bin will always have Shelf as a parent, but if Shelf is disabled, there will only be one Shelf entity created and it will be hidden from view.

Option 2
In each parent under Warehouse, create a linking field that varies based on the setting of the Warehouse. For example, if the settings for the Warehouse are Zone, Row, Bin, then in the Zone table, specify Row as the linking child, and then link to Row table.

I would like to follow "best practices" database design, and also create the most efficient structure possible. I would appreciate your thoughts.

5
  • Option 3: Use a bill of materials structure. Commented Jun 24 at 20:23
  • In a BOM structure, you have Items (raw materials, sub-assembly, and finished goods) in an Item table, and the BOMs that establish the relationships between Items to get to a finished good (e.g., a BOM to connect raw material to a sub-assembly, and a BOM to connect the sub-assembly to a finished good). Following this structure for the warehouse scenario, there would be one table to hold storage locations (e.g., Warehouse, Zone, Aisle, etc), and then another table to hold the relationship between the storage locations for each given situation (e.g., connecting a Warehouse to Zones), right?
    – Zack
    Commented Jun 25 at 1:52
  • Assuming all your location relationships are one to many, you only need one table. One warehouse has many zones. One zone has many aisles. One aisle has many rows. You need another table to list all the possible locations with a boolean to determine if the location is part of a particular warehouse. Commented Jun 25 at 5:25
  • Thanks! So if I'm understanding you correctly, this is basically an Agency List Model with an extra table to determine the allowed storage locations for a given Warehouse...right?
    – Zack
    Commented Jun 25 at 16:26
  • Yes, you are correct. Commented Jun 25 at 18:59

0