Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

I am trying to model the processes and include parent processes and sequential order of processes.

For example:

Process Table
---------------------
ProcessID - uniqueidentifier
ProcessName - varchar
ProcessDescription - varchar
...

ProcessOrder Table
---------------------
ProcessID - uniqueidentifier FK - Process
ParentProcessID - uniqueidentifier FK - Process
ProcessOrder - int
...

The ProcessOrder column in the ProcessOrder Table would simply store a number representing which sequential step in the parent process it represents.

For example, a modeling procedure has the following steps: create new empty model, name model, enter model parameters. The Process Table would look like:

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

The ProcessOrder Table would look like:

ProcessID | ParentProcessID | ProcessOrder
--------------------------------------------------
UUID2     | UUID1           | 1
UUID3     | UUID1           | 2
UUID4     | UUID1           | 3

The issue with this design is that when the workflow gets updated, the process order will change and I will need to update the ProcessOrder record for the process that changed and for all subsequent records with the same ParentProcessID.

Is there a better way to store this kind of data and maintain normalization?

share|improve this question
    
Small note: will your ProcessOrder tables have a generated PK (presumably a GUID)? { ProcessID | ProcessOrder } should probably have a UK, but wouldn't be ideal as a PK given the changing values in ProcessOrder. –  Jon of All Trades Jun 8 '12 at 16:26
add comment

4 Answers

up vote 0 down vote accepted

Your design seems reasonable to me. While you do have to update all subsequent records when new processes are added or deleted that is easy to accomplish. You just issue an update like:

UPDATE ProcessOrder
SET ProcessOrder = ProcessOrder+1
WHERE ProcessOrder >= [step# where you want to insert]

and then do your insert or delete.

The only other way I can think of would be to design the schema to store the next process id on the row. Something like:

ProcessID | ParentProcessID | NextId
--------------------------------------------------
UUID2     | UUID1           | UUID3
UUID3     | UUID1           | UUID4
UUID4     | UUID1           | NULL

Then if you insert a new step - say between UUID3 and UUID4, you perform more of a linked list operation which will update UUID3|UUID1's NextId to UUID5 and then just insert the new UUID5 with a NextId of UUID4.

This will reduce the UPDATEs to 1 in most cases, but it will make querying the process more difficult as now you have to walk the list from top to bottom to list out step by step.

You need to decide which process you want to favor - inserting and updating or retrieving. If you favor retrieval (which you might if changes are infrequent and reporting is frequent, and the lists are short), then go with your original design. If you favor insert and update (which you might if changes are happening all the time and reporting is infrequent, or lists are really really long), then go with the linked list approach.

I hope this helps. Interested in what other solutions the community might come up with as I'd love to broaden my knowledge around this!

share|improve this answer
    
Thanks for your response. It is very helpful. I think I will be more focused on the querying side of things than inserting or updating. So, it sounds like I should use your first example. Could you advise how I might put the update statement into a trigger? –  Brian Jun 8 '12 at 13:51
    
Inserts would violate your primary key so you have to create an instead of insert trigger. In that trigger you would have to check if the inserted record will violate the PK, and if it will then perform the update I described using the ProcessOrder from the INSERTED table as the step#. Then perform the INSERT. For deletes you will need to write a BEFORE DELETE trigger that will perform the UPDATE I described using ProcessOrder from the DELETED table as the step#. No checks would be necessary. You do NOT want to have any UPDATE triggers as they will fire when your INS/DEL triggers fire. –  Todd Everett Jun 8 '12 at 20:03
add comment

If all you need is to store which step of your process comes after which previous step, then all you need is the following:

ProcessID | ParentProcessID | PreviousProcessID

Of course, you will need a FK constraint to make sure that (ParentProcessID | PreviousProcessID) points to a valid (ParentProcessID | ProcessID)

If I understood your requirements and this design is valid, then it is easy to insert/remove/move around steps in your process - you do not have to propagate any changes to your child tables, because they refer to your primary key on (ParentProcessID | ProcessID).

HIH

share|improve this answer
add comment

Couple of questions first...

  1. Why are you going to use unique identifiers as your key columns? I see this done often in databases and I'm never sure why. If you truly need a record to be unique throughout the entire database, or even across multiple systems/databases it's perfectly fine. However, if that's not the case and you just need the record to be unique in the table then use an integer value. Even if you have to use a BIGINT you'll be better off; a BIGINT type is 8 bytes whereas a UNIQUEIDENTIFIER is stored as a 16 byte binary string.
  2. What does the workflow look like at the application level in relation to updating the process order? The reason I ask is because you might be able to avoid multiple UPDATE statements if you can store everything in local memory until the workflow is complete. For example, if the front end application is an ASP.NET Web application you could store everything in Session State, allow users to complete the entire workflow, and then perform a single database operation.
  3. Is it possible that multiple users will be working components of the same process workflow concurrently? If so, you will need to put checks in place that ensure two users cannot step on each other's toes.
  4. What about a process history for auditing and reporting? If you update records then you will completely lose all of the history. You'll never be able to present a report that shows the different process steps, the time it took to move from one to the other, etc.

Three and four above can be solved by inserting records for every single change instead of updating records. This will obviously create a ton of additional data, but it will also give you a ton of additional insight into the workflow itself and eventually provide information that can be used for trending, PKI's and other business intelligence, which brings us to data warehousing... But that's another post.

share|improve this answer
add comment

The process step is not meaningful in your case unless a process version is defined. So you could say that process 1 has its steps executed in this order (a, b , d, c) when the process was in version 1, but in version 2 the step execution order changed to be (a,b,c). So I think that a process version is important.

The diagram below represents my suggestion.

The silly thing about this is that if you change the order of a step, you have to insert all steps again in the new order, but in this case, it won't matter either in space or time.

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.