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?

4
  • What's the maximum length of MyString?
    – McNets
    Aug 29, 2019 at 10:02
  • Lets say it has a max length of 500.
    – WebbyLass
    Aug 29, 2019 at 10:16
  • Must it be a relational database solution? Aug 29, 2019 at 11:40
  • We are thinking it has to be but if you have an idea on how to solve it without it being relational...?
    – WebbyLass
    Aug 29, 2019 at 12:11

1 Answer 1

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

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.