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.
boolean
to determine if the location is part of a particular warehouse.