I am working on designing a database model that involves tracking processes almost like an assembly line. There is only one type of item (lets call it 'unit') that has about 10 different tasks done to it. The order of operations performed on the unit is not static and each task could potentially be done many times. However, each task has its own set of associated attributes.
What I currently have is a 'Units' table and a table for each task. Each task table's attributes are the necessary parameters involved with that task. Then there is a table that relates the tasks to the Units. This 'TaskParent' table has foreign key fields for each task and a foreign key field for the UnitId. When inserting only one task field is not null, this way I get a relationship from the unit to the Task details (Unit -> Task -> Task details).
A few things to answer the 'What do I need to know?' when using this data model:
- Get all items currently at a specific task
- Get all tasks a Unit has encountered
- Compare Units based on various task details
- There is much more but I am trying to simplify it to my 'current lack of understanding' (I'm sure there will be more of that..)
My question is does anyone know of a design pattern for this type of situation or have input in how this should be implemented?
I have looked at this.
This is my first question on Stack, but I have had thousands of questions answered by this site. Thanks to all those who share their knowledge!