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?
Best Answer
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.
Show the order details:
Results:
Cleanup: