0

I am trying to model hierarchical data using a RDBMS but can't quite find the right solution. We are capturing information about storage levels in a warehouse system. There are 8 possible storage levels, each with a parent-child relationship to levels above and below. Stock is then associated with the final level (Level 8). (BTW each level has a name but I'm keeping it generic for simplicity.)

Warehouse 1
  Level 1
    Level 2
      Level 3
        Levels 4-7...
          Level 8 -> associated with stock

You will always have Level 8, but you may not always have every parent Level. Here is the added complexity that is confusing me:

  1. Levels can be skipped, making the hierarchy ragged as opposed to balanced. For example, a Level 6 might have Level 4 as a parent and skip Level 5.
  2. The parent to a Level MUST be a level above in the hierarchy, i.e., Level 5 cannot have Level 6 as a parent; it can only be Levels 4 or less.
  3. Child Levels inherit the properties of their parent. This means that if we designate a Level for "Receiving", this same property will be inherited by all child Levels.
  4. Levels can be disabled by a parent. This means in a structure where you have Level 1 -> Level 2 -> Level 8, the user may later disable Level 2, making the hierarchy now Level 1 -> Level 8 and we will need to readjust the parent of all Level 8 entries in the lineage.
  5. As a reminder, we always end up at Level 8.

I have researched a lot but am still struggling with some things. Here's the open items I'm not sure about.

  1. One route I can think of is to put all Levels in the same table, and prepare a seemingly standard adjacency list model where each Level points to its parent. I'm not sure if this is the best approach due to the added constraints based on Level Type; i.e., Levels cannot choose lower Levels as a parent, and also some parents may have disabled certain child Levels. We also need to manage inherited properties at each Level based on the properties of its parent.
  2. I'm leaning towards placing each Level in a separate table, and then transforming the ragged hierarchy into a balanced hierarchy by using dummy Levels (unseen to the user) when they are skipped. In this way, Level 3 is always the parent to Level 4, for example, even if the user selects Level 2 as the parent of Level 4. This was a solution I read about in this SE post.

If you have better ideas or insights to share, please do. I'm new at this and eager to learn so I apologize if some of my questions appear to be basic.

Improve this question
2
  • 1
    modern databases have recursive ctes which can return such data easily so you need only to save ( id, name , parent) Commented Aug 2, 2024 at 18:26
  • Can you give some example data explaining what those level are about? Initially i thought it was something like building - aisle - rack - shelf, but your mention about a "Receiving" level indicates something else Commented Aug 4, 2024 at 17:06

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.