We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure.

We generate XML files from the database, which our software application uses to load and display the required data. As the complexity of the data used by the application increases, predominantly becoming more hierarchical and using more arrays, the current database structure is obviously not suitable for purpose.

We have discussed the possibility of the using a parent-child relationship for parameters that have an array of values, using an index to order the data. However, this does not solve the problem of storing data structures and arrays of data structures within a data structure within a database.

A typical data structure used within the application looks like the following:

class A {
    int MyInt;
    List<B> MyBs;
    C MyC; }

class B {
    string MyString;
    double[] MyNumbers; }

class C {
    string MyString;
    double MyDouble;
    B MyB;
    List<B> MyBs; }

Can anyone help advise us on how we might solve this issue?

  • 3
    What hinders you to create a classic relational datamodel, using database 101, normalization, as well as classic parent/child relationships? With tables A, B, C, having an ID column and B foreign key relations to A.ID as well as to C.ID, and another table D for the double arrays in B? – Doc Brown Sep 6 '19 at 12:01
  • Would a document db be an alternative ? – Christophe Sep 6 '19 at 22:32
  • please don't cross-post: dba.stackexchange.com/questions/246534/… "Cross-posting is frowned upon as it leads to fragmented answers splattered all over the network..." – gnat Sep 7 '19 at 15:41

To store hierarchical data in a relational database you have a few choices of techniques. Depending on your use case (read-heavy, write-heavy, implementation complexity, etc.) you may want to evaluate them given your constraints and choose then.

Nested sets


left     right     key     value
1        6         a
2        5         b
3        4         c       1

Pro: good read performance, possible to query sub-trees efficiently

Con: abysmal write performance as adjacent entries have to be updated on inserts. Could possibly be optimized, but that would increase code complexity.

Parent child


id     parent      key     value
1      NULL        a
2      1           b
3      2           c       1

Pro: conceptually simple, efficient to query direct descendants

Con: requires complex self-joins to query for sub trees

Materialized path


key     value
a.b.c   1

Pro: conceptually simple, inserting a leaf inserts all the previous nodes by definition, efficient to query sub-trees when using prefix indexes.

Con: database won't help you in regards to data-consistency.

Postgres JSON column


{"a": {"b": "c": 1}}}

Pro: simple implementation, supports maps and lists out of the box. Querying of sub-elements is also possible. Not sure if SQL Server has something similar.

Con: Postgres only.


My examples only only handled the simple case of a scalar value, though you can of course extend the data schema to support more complex types.

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