Database Design - Hierarchical Structure [closed]

Question

1

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?

Answer 1

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

Example:

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

Example:

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

Example:

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

Example:

values
{"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.

Discussion

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.