Design database with versioning and without redundant information

Question

I have the following tables:

Orders -> Procedures -> Steps -> Tasks -> NeededMaterials -> Materials

When I receive an order I want to generate procedures for its execution. The procedures are different depending on the order and its parameters. Each procedure has some steps and each step contains several tasks. For each task are assigned materials. In each procedure, the steps, tasks, and materials may be different, depending on the order's parameters, so I want to generate and store them.

All of the Procedures, Steps, Tasks and NeededMaterials are predefined, but at any time they may be updated(changed) and I need to keep track of all of the executed(generated) procedures with their steps, tasks, and materials. I need the possibility to trace back an order for example that was generated a year ago, and since then the procedure has been changed several times.

The problem is that I don't know how to do it while avoiding to store redundant information. Is it actually possible? Is there a design pattern? I can easily create another set of tables with the generated content, but then on every order I will store all of its data(i.e. all material names), because everything may be changed at any time.

If there is no way of avoiding redundancy, is NoSQL database a better approach?

Answer 1

I'd avoid using a NoSQL database for what is clearly relational data.

As far as I can tell, this design provided below might work for your system by providing versioning for all steps, components, materials, etc, without requiring unnecessary redundancy. Clearly, if there is a new version of a given material, some of the material meta-data may not change, such as the material name, so there will naturally be some redundancy; however this design keeps that to a minimum.

USE tempdb;

CREATE TABLE dbo.Procedures
(
    ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , ProcedureName varchar(30) NOT NULL
    , CONSTRAINT PK_Procedures
        PRIMARY KEY CLUSTERED
        (
            ProcedureID
            , ProcedureVersionNum
        )
);

INSERT INTO dbo.Procedures (ProcedureID, ProcedureVersionNum, ProcedureName)
VALUES (1, 1, 'Create SQL Server');

CREATE TABLE dbo.Steps
(
    StepID int NOT NULL
    , StepVersionNum int NOT NULL
    , StepName varchar(30) NOT NULL
    , ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , ProcedureStepOrder int NOT NULL
    , CONSTRAINT FK_Steps_Procedure
        FOREIGN KEY (ProcedureID, ProcedureVersionNum)
        REFERENCES dbo.Procedures (ProcedureID, ProcedureVersionNum)
    , CONSTRAINT PK_Steps
        PRIMARY KEY CLUSTERED
        (
            StepID
            , StepVersionNum
        )
);

INSERT INTO dbo.Steps (StepID, StepVersionNum, StepName, ProcedureID, ProcedureVersionNum, ProcedureStepOrder)
VALUES (1, 1, 'Deploy Virtual Machine', 1, 1, 1)
    , (2, 1, 'Install SQL Server', 1, 1, 2)
    , (3, 1, 'Configure SQL Server', 1, 1, 3);

CREATE TABLE dbo.Tasks
(
    TaskID int NOT NULL
    , TaskVersionNum int NOT NULL
    , TaskName varchar(30) NOT NULL
    , StepID int NOT NULL
    , StepVersionNum int NOT NULL
    , TaskStepOrder int NOT NULL
    , CONSTRAINT FK_Tasks_Step
        FOREIGN KEY (StepID, StepVersionNum)
        REFERENCES dbo.Steps (StepID, StepVersionNum)
    , PRIMARY KEY CLUSTERED 
        (
            TaskID
            , TaskVersionNum
        )
);

INSERT INTO dbo.Tasks (TaskID, TaskVersionNum, TaskName, StepID, StepVersionNum, TaskStepOrder)
VALUES (1, 1, 'Create VM', 1, 1, 1)
    , (2, 1, 'Install Operating System', 1, 1, 2)
    , (3, 1, 'Join VM to Domain', 1, 1, 3)
    , (4, 1, 'Run SQL Server Setup', 2, 1, 1)
    , (5, 1, 'Configure SQL Server Security', 3, 1, 1)
    , (6, 1, 'Create databases', 3, 1, 2);

CREATE TABLE dbo.Materials
(
    MaterialID int NOT NULL
    , MaterialVersionNum int NOT NULL
    , MaterialName varchar(30) NOT NULL
    , CONSTRAINT PK_Materials
        PRIMARY KEY CLUSTERED
        (
            MaterialID
            , MaterialVersionNum
        )
);

INSERT INTO dbo.Materials (MaterialID, MaterialVersionNum, MaterialName)
VALUES (1, 1, 'Virtual Machine')
    , (2, 1, 'Operating System Install Media')
    , (3, 1, 'SQL Server Install Media')
    , (4, 1, 'Disk');

CREATE TABLE dbo.NeededMaterials
(
    NeededMaterialID int NOT NULL
    , NeededMaterialVersionNum int NOT NULL
    , MaterialID int NOT NULL
    , MaterialVersionNum int NOT NULL
    , CONSTRAINT FK_NeededMaterials_Material
        FOREIGN KEY (MaterialID, MaterialVersionNum)
        REFERENCES dbo.Materials (MaterialID, MaterialVersionNum)
    , TaskID int NOT NULL
    , TaskVersionNum int NOT NULL
    , CONSTRAINT FK_Tasks
        FOREIGN KEY (TaskID, TaskVersionNum)
        REFERENCES dbo.Tasks (TaskID, TaskVersionNum)
    , PRIMARY KEY CLUSTERED 
        (
            NeededMaterialID
            , NeededMaterialVersionNum
        )
);

INSERT INTO dbo.NeededMaterials (NeededMaterialID, NeededMaterialVersionNum, MaterialID, MaterialVersionNum, TaskID, TaskVersionNum)
VALUES (1, 1, 1, 1, 1, 1)
    , (2, 1, 2, 1, 2, 1)
    , (3, 1, 3, 1, 4, 1)
    , (4, 1, 4, 1, 1, 1)

CREATE TABLE dbo.Orders
(
    OrderID int NOT NULL
        PRIMARY KEY CLUSTERED
    , OrderDate datetime NOT NULL
    , CustomerName varchar(30) NOT NULL
);

INSERT INTO dbo.Orders (OrderID, OrderDate, CustomerName)
VALUES (1, '2018-03-15 09:24:00', 'Max Vernon');

CREATE TABLE dbo.OrdersProcedures
(
    OrderProceduresID int NOT NULL
        CONSTRAINT PK_OrdersProcedures
        PRIMARY KEY CLUSTERED
    , OrderID int NOT NULL
    , ProcedureID int NOT NULL
    , ProcedureVersionNum int NOT NULL
    , CONSTRAINT FK_OrdersProcedures_Procedures
        FOREIGN KEY (ProcedureID, ProcedureVersionNum)
        REFERENCES dbo.Procedures (ProcedureID, ProcedureVersionNum)
);

INSERT INTO dbo.OrdersProcedures (OrderProceduresID, OrderID, ProcedureID, ProcedureVersionNum)
VALUES (1, 1, 1, 1);

Show the order details:

SELECT o.CustomerName
    , o.OrderDate
    , p.ProcedureName
    , p.ProcedureVersionNum
FROM dbo.Orders o
    INNER JOIN dbo.OrdersProcedures op ON o.OrderID = op.OrderID
    INNER JOIN dbo.Procedures p ON op.ProcedureID = p.ProcedureID AND op.ProcedureVersionNum = p.ProcedureVersionNum;

╔══════════════╦═════════════════════════╦═══════════════════╦═════════════════════╗
║ CustomerName ║        OrderDate        ║   ProcedureName   ║ ProcedureVersionNum ║
╠══════════════╬═════════════════════════╬═══════════════════╬═════════════════════╣
║ Max Vernon   ║ 2018-03-15 09:24:00.000 ║ Create SQL Server ║                   1 ║
╚══════════════╩═════════════════════════╩═══════════════════╩═════════════════════╝

SELECT p.ProcedureName
    , s.StepName
    , t.TaskName
    , t.TaskStepOrder
    , m.MaterialName
FROM dbo.Procedures p
    LEFT JOIN dbo.Steps s ON p.ProcedureID = s.ProcedureID AND p.ProcedureVersionNum = s.ProcedureVersionNum
    LEFT JOIN dbo.Tasks t ON s.StepID = t.StepID AND s.StepVersionNum = t.StepVersionNum
    LEFT JOIN dbo.NeededMaterials nm ON t.TaskID = nm.TaskID AND t.TaskVersionNum = nm.TaskVersionNum
    LEFT JOIN dbo.Materials m ON nm.MaterialID = m.MaterialID AND nm.MaterialVersionNum = m.MaterialVersionNum
ORDER BY p.ProcedureName
    , s.ProcedureStepOrder
    , t.TaskStepOrder;

Results:

╔═══════════════════╦════════════════════════╦═══════════════════════════════╦═══════════════╦════════════════════════════════╗
║   ProcedureName   ║        StepName        ║           TaskName            ║ TaskStepOrder ║          MaterialName          ║
╠═══════════════════╬════════════════════════╬═══════════════════════════════╬═══════════════╬════════════════════════════════╣
║ Create SQL Server ║ Deploy Virtual Machine ║ Create VM                     ║             1 ║ Virtual Machine                ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Create VM                     ║             1 ║ Disk                           ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Install Operating System      ║             2 ║ Operating System Install Media ║
║ Create SQL Server ║ Deploy Virtual Machine ║ Join VM to Domain             ║             3 ║ NULL                           ║
║ Create SQL Server ║ Install SQL Server     ║ Run SQL Server Setup          ║             1 ║ SQL Server Install Media       ║
║ Create SQL Server ║ Configure SQL Server   ║ Configure SQL Server Security ║             1 ║ NULL                           ║
║ Create SQL Server ║ Configure SQL Server   ║ Create databases              ║             2 ║ NULL                           ║
╚═══════════════════╩════════════════════════╩═══════════════════════════════╩═══════════════╩════════════════════════════════╝

Cleanup:

DROP TABLE dbo.OrdersProcedures;
DROP TABLE dbo.Orders;
DROP TABLE dbo.NeededMaterials;
DROP TABLE dbo.Materials;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Steps;
DROP TABLE dbo.Procedures;