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.
I am also constrained to using PostgresSQL.
Happy to add more details.