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;