title: Database Design for updatable sequential records
body: <p>I am working on creating a data model for storing data related to production tracking.  I work for an engineering firm that models and analyzes data for our clients.  There are several steps in the process and the process is constantly updating.</p>
  
  <p>I am trying to model the processes and include parent processes and sequential order of processes.</p>
  
  <p>For example:</p>
  
  <pre><code>Process Table
  ---------------------
  ProcessID - uniqueidentifier
  ProcessName - varchar
  ProcessDescription - varchar
  ...
  
  ProcessOrder Table
  ---------------------
  ProcessID - uniqueidentifier FK - Process
  ParentProcessID - uniqueidentifier FK - Process
  ProcessOrder - int
  ...
  </code></pre>
  
  <p>The <code>ProcessOrder</code> column in the <code>ProcessOrder</code> Table would simply store a number representing which sequential step in the parent process it represents.</p>
  
  <p>For example, a modeling procedure has the following steps: create new empty model, name model, enter model parameters.  The <code>Process</code> Table would look like:</p>
  
  <pre><code>ProcessID | ProcessName | ProcessDescription
  -------------------------------------------------
  UUID1     | Modeling    | Create Model of Data
  UUID2     | New Model   | create new empty model
  UUID3     | Name Model  | name model
  UUID4     | Parameters  | enter model parameters
  </code></pre>
  
  <p>The <code>ProcessOrder</code> Table would look like:</p>
  
  <pre><code>ProcessID | ParentProcessID | ProcessOrder
  --------------------------------------------------
  UUID2     | UUID1           | 1
  UUID3     | UUID1           | 2
  UUID4     | UUID1           | 3
  </code></pre>
  
  <p>The issue with this design is that when the workflow gets updated, the process order will change and I will need to update the <code>ProcessOrder</code> record for the process that changed and for all subsequent records with the same <code>ParentProcessID</code>.</p>
  
  <p>Is there a better way to store this kind of data and maintain normalization?</p>