Database Design - hierarchical structure

Question

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

This could be a possible model, depending on the length of MyString, you could replace surrogated keys on B and C by this column.

CREATE TABLE B
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [MyString] nvarchar(500),

    CONSTRAINT [PK_B] PRIMARY KEY ([ID])
);

CREATE TABLE C
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [MyString] nvarchar(500),
    [MyDouble] decimal(18,2) DEFAULT(0),
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_C] PRIMARY KEY ([ID]),
    CONSTRAINT [FK_C_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B([ID])
);

CREATE TABLE A
(
    [ID] int NOT NULL,
    [C_ID] int NOT NULL,

    CONSTRAINT [PK_A] PRIMARY KEY ([ID]),
    CONSTRAINT [FK_A_C_ID] FOREIGN KEY ([C_ID]) REFERENCES C ([ID])
);

LIST/ARRAYS

CREATE TABLE A_B_LIST
(
    [A_ID] int NOT NULL,
    [Index] int NOT NULL, /* Index of array */
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_A_B_LIST] PRIMARY KEY ([A_ID], [Index]),
    CONSTRAINT [FK_A_B_LIST_A_ID] FOREIGN KEY ([A_ID]) REFERENCES A ([ID]),
    CONSTRAINT [FK_A_B_LIST_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B ([ID]),
);

CREATE TABLE B_NUM
(
    [B_ID] int NOT NULL,
    [Index] int NOT NULL,
    [MyNumber] decimal(18,2),

    CONSTRAINT [PK_B_NUM] PRIMARY KEY ([B_ID], [Index]),
    CONSTRAINT [FK_B_NUM_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B([ID])
);

CREATE TABLE C_B_LIST
(
    [C_ID] int NOT NULL,
    [Index] int NOT NULL,
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_C_B_LIST] PRIMARY KEY ([C_ID], [Index]),
    CONSTRAINT [FK_C_B_LIST_C_ID] FOREIGN KEY ([C_ID]) REFERENCES C ([ID]),
    CONSTRAINT [FK_C_B_LIST_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B ([ID])
);

db<>fiddle here