2

I have a UX that is modeling a hierarchy. There is a concept of a section, a sub-section under a section, a panel under a section and a bunch of form-fields under a panel.

The UX exposes an ability to (valid-ly) reorder the hierarchy (move a panel to a different sub-section or a sub-section to a different section, etc.) and a save button that will save all of this in the backend.

My question is as to how do we store this structure while exposing a meaningful API. My intuition is to continue to use SQL tables for each of these entities with appropriate FK's.
However, since the save button saves the entire structure in one go, the backend would have to compare all the elements of this structure and update the appropriate entity rows. This for some reason feels "heavy" and something feels inelegant about this.

The other approach is to use a JSON store and just store and retrieve the exact structure, but I don't want to lose the flexibility of being able to control each of the individual entities.

My tables would like this :

Section :
section_id | subsection_id | other_section_related_columns

SubSection : 
subsection_id | panel_id | other_subsection_related_columns

Panel : 
panel_id | field_id | other_panel_related_columns

Field : 
field_id | other_field_related_columns

A sample JSON would like the following :

{
  "sections": [
    {
      "section_id": "1",
      "section_name": "Section 1",
      "subsections": [
        {
          "sub_section_id": "1",
          "panels": [
            {
              "panel_id": "1",
              "fields": [
                {
                  "field_id": "1",
                  "field_name": "field 1"
                }
              ]
            }
          ]
        },
        {
          "sub_section_id": "2",
          "panels": [
            {
              "panel_id": "2",
              "fields": [
                {
                  "field_id": "2",
                  "field_name": "field 2"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

I am also constrained to using PostgresSQL.

Happy to add more details.

3
  • 1
    Could you perhaps sketch out your multiple table solution or how you'd model the data with Json? Perhaps you just need a Postgres array column?
    – amon
    Jul 14, 2021 at 7:27
  • Thanks @amon for your comment. Added some details on what I am proposing for a schema and json representation. Jul 14, 2021 at 15:00
  • See stackoverflow.com/questions/30274141/…
    – NeeL
    Jul 14, 2021 at 21:20

1 Answer 1

2

Since you want to treat the various parts of your hierarchy as independent entities, storing them in separate tables would seem to make sense. The upside is that you can have rich relationships and constraints between them, the downside is that querying the hierarchy is far more tricky – but not impossible. For example, you could create a stored procedure that converts these hierarchy elements to a corresponding JSON document.

Updating this relational representation will require multiple statements, or a stored procedure call that executed the multiple statements. That seems heavy, but mostly because SQL can be very verbose. You do not have to filter out existing entries in advance, and can use upserts (insert ... on conflict do update) to save the contents of the hierarchy.

A JSON-based representation seems much more convenient. Indeed, this can be a suitable design, and Postgres makes a fine document database. However, it is much harder to apply constraints such as foreign-key relationships. You can do that, it's just much more tricky. However, you cannot easily join other data with information within a JSON document.

This points to an important aspect: to which degree are these hierarchies isolated? Do they share information, do other tables reference parts of the hierarchies? For example, if multiple subsections share the same panel, you cannot reasonably represent that using JSON, but can trivially represent this in the relational design.

2
  • Thanks @amon for the response. The relationships of the entities does not extend beyond this hierarchical set-up. And also it can be modeled as an n-ary tree and so we will not have multiple sub-sections containing the same panel. There was also a requirement to only save the entire tree and not individual components and so I feel like building out a nice data-layer that can abstract out the difference between the old and new structures might help. Jul 20, 2021 at 19:54
  • @thispatchofsky Sounds like a JSON document data model would be a better fit, then. You could always write triggers or stored procedures that extract a relational view from the JSONB column, but that might be difficult since the JSON types (e.g. Number, Object) don't always have a directly equivalent SQL type.
    – amon
    Jul 21, 2021 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.