title: Best Practices: Storing a workflow state of an item in a database?
body: <p>I have a question about best practices regarding how one should approach storing complex workflow states for processing tasks in a database.  I've been looking online to no avail, so I figured I'd ask the community what they thought was best.</p>
  
  <p>This question comes out of the same "BoxItem" example I gave in a prior question.  This "BoxItem" is being tracked in my system as various tasks are performed on it.  The task may take place over several days and with human interaction, so the state of the BoxItem must be persisted.  Who did the task (if applicable), and when the task was done must also be tracked.</p>
  
  <p>At first, I approached this by adding three fields to the "BoxItems" table for every human-interactive task that needed to be done:</p>
  
  <p>Is<em>TaskName</em>Complete</p>
  
  <p>Date<em>TaskName</em>Complete</p>
  
  <p>User<em>TaskName</em>Complete</p>
  
  <p>This worked when the workflow was simple... but now that it has grown to a complex process (> 10 possible human interactions in the flow... about half of which are optional, and may or may not be done for the BoxItem, which resulted in me beginning to add "Do<em>TaskName</em>" fields as well for those optional tasks), I've found that what should've been a simple table now has 40 or so field devoted entirely to the retaining of this state information.</p>
  
  <p>I find myself asking if there isn't a better way to do it... but I'm at a loss.</p>
  
  <p>My first thought was to make a generic "BoxItemTasks" table which defined the tasks that may be done on a given box, but I still would need to save the Date and User information individually, so it didn't really help.</p>
  
  <p>My second thought was that perhaps it didn't matter, and I shouldn't worry if this table has 40 or more fields devoted to state retaining... and maybe I'm just being paranoid.  But it feels like that's a lot of information to retain.</p>
  
  <p>Anyways, I'm at a loss as far as what a third option might be, or if one of the two options above is actually reasonable.  I can see this workflow potentially getting even more complex in the future, and for each new task I'm going to need to add 3-4 fields just to support the tracking of it... it feels like it's spiraling out of control.</p>
  
  <p>What would you do in this situation?</p>
  
  <p>I should note that this is maintenance of an existing system, one that was built without an ORM, so I can't just leave it up to the ORM to take care of it.</p>
  
  <p>EDIT:</p>
  
  <p>Kev, are you talking about doing something like this:</p>
  
  <h2>BoxItems</h2>
  
  <p>(PK) BoxItemID</p>
  
  <p>(Other irrelevant stuff)</p>
  
  <h2>BoxItemActions</h2>
  
  <p>(PK) BoxItemID</p>
  
  <p>(PK) BoxItemTaskID</p>
  
  <p>IsCompleted</p>
  
  <p>DateCompleted</p>
  
  <p>UserCompleted</p>
  
  <h2>BoxItemTasks</h2>
  
  <p>(PK) TaskType</p>
  
  <p>Description (if even necessary)</p>
  
  <p>Hmm... that would work... it would represent a need to change how I currently approach doing SQL Queries to see which items are in what state, but in the long term something like this looks like it would work better (without having to make a fundamental design change like the Serialization idea represents... though if I had the time, I'd like to do it that way I think.).</p>
  
  <p>So is this what you were mentioning Kin, or am I off on it?</p>
  
  <p>EDIT: Ah, I see your idea as well with the "Last Action" to determine the current state... I like it!  I think that might work for me... I might have to change it up a little bit (because at some point tasks happen concurrently), but the idea seems like a good one!</p>
  
  <p>EDIT FINAL:  So in summation, if anyone else is looking this up in the future with the same question... it sounds like the serialization approach would be useful if your system has the information pre-loaded into some interface where it's queryable (i.e. not directly calling the database itself, as the ad-hoc system I'm working on does), but if you don't have that, the additional tables idea seems like it should work well!  Thank you all for your responses!</p>