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'm trying to model a workflow (status and activities) to create "rules".

Let me give you an example.

The main "object" let's say are "vehicle requirements", I will have this table:

idRequirement, idVehicle,DateOfRequirement, idStatus, LastUpdate
1                2       2012-01-02 10:20:00, 1     ,2012-01-02 10:20:00

In the example above as the register is just created, the DateOfRequirement and LastUpdate is the same, LastUpdate it will be updated after every change of status.

Status TAble

 idStatus,Status
 1         New
 2         Vehicle Assigned
 3         Closed

Activities Table

 idActivitie, Activitie
 1            Create Vehicle Requirement
 2            Assign Vehicle
 3            Close requirement.
 4            ReAssign Vehicle.
 5            Confirm

Until the tables above I have no problems, but my problem is that for example:

I know I cannot close a requirement (update to idStatus=3 on "vehicle requirements") if the current status is not "Vehicle Assigned" (idStatus=2).

I can do that by code (or I think so), but is there anyway to model this kind of rules to tables ? I mean a table where I retrieve the next idStatus and the activitie to perform (to save a "kardex/historical" of each requirement and it's activities), checking for example the Activitie or the current status ?

The rules also will have 3 "alerts" accordin the time and status. For example the limit time "green" to assign vehicle is 30 minutes or less the yellow "alert" it will be between 31 and 45 after the requirement has been created (status new) and the red alert it will show it after 45 of being in status "new"

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Green

After 39 minutues it will something like this:

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Yellow

And after 45 minutes:

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Red

If there's no enough clear please tell me to try to explain better.

In the meantime I just need a single "workflow" I mean, always the order it will be idStatus=1, after that idStatus=2 and aftear idStatus=3, but if you want to help me considergin cases like go to idStatus=3 after idStatus=1, I'll be really grateful.

Some idea (probably bad) I had:

Table rules:

idRule   Name      idCurrentState  idNextState  idActivitieToPerform  GreenLimit  YelloLimit 
1        Assign            1             2           1                  30          45     
2        CloseFromNew      1             3           3                  30          90
3        ChangeVehicle     2             2           4                  45          90
4        CloseRequirement  2             3           3                  90          120
5        ConfirmVehicle    2             2           5                  30          90

But I have lot of trouble because of the "idCurrentState" and "idNextState" refers to the same primary key on status table. My regards and thanks for your time.

Updates:

  • Remove "redLimit" (as Kevin Feasel mentioned (and I figure out a couple of hours after post, it's implied))
  • Add some Rules to let the sample more clear:

    • CloseFromNew
    • ChangeVehicle
    • CloseRequirement
  • Add activitie "ReAssign Vehicle"

To consider:

  • I'm 99% sure there are only 3 "colors" (so we can't ignore relationships with Colors and any other table as status/activities).

  • Different activities derive on the same status (ChangeVehicle and Confirm) (muliple activities)

  • Different activities in different status derive on same status (no linear workflow)

  • No linear workflow

    • "CloseFromNew" perform "Close" activity and set Status to "Closed" from status "New", just as "CloseRequirement" set status to "Closed" but from status "Vehicle Assigned"

Well based on the comments of Kevin Feasel, I just extend my sample/problem (after "Update" text explain the constraints I need to accomplish)

My regards again !

share|improve this question

1 Answer 1

up vote 4 down vote accepted

What you have can work, given a few constraints:

  • You move between states in a linear fashion
  • You have multiple states with multiple activities.
  • You have only one consideration for what "color" the alert indicator should be.
  • You have three colors and only three colors (no more and no fewer).

Based on what you mentioned as requirements, that solution fits most of the constraints, but not #2. There are a couple of things I would look at, though.

One thing I would question is whether you need that Activity table at all. If it really is a 1:1 correspondence between states and activities, you could wrap it up into one table without loss: your activity is just the action which pushes you to the next state. If, however, there are multiple activities per state, this can make sense. If you have an overarching status category (e.g., when the row is in "Vehicle Assigned" status, there are 5 separate activities which need to occur to get it to "Closed" status), then this general idea is alright.

So if you have one activity per state, the Status table would have the following attributes:

  1. Name
  2. Activity required to get to this state (if you, for example, have a web application and you want to populate the button text with a verb instead of a noun)
  3. The next status ID (assuming this is a linear workflow)
  4. GreenMax (the maximum number of minutes during which the row can be "green")
  5. YellowMax (the maximum number of minutes during which the row can be "yellow")

RedMax is implied: it's red whenever the difference between the current time and whichever time you want to use (DateOfRequirement, LastUpdate, etc.) is greater than YellowLimit.

Alternatively, if you have different categories for display such as "step 1 has red-yellow-green, but step 2 is just red-green and step 3 is red-pink-orange" (or if you believe that you will), I would move the colors out to their own table and create a Status-Color bridge table with status ID, color ID, and maximum time. That makes retrieval a bit more complicated, though.

Now, if you do have multiple activities per status, then I would turn Status into a parent-child relationship with Activity, instead of a many-to-many relationship like you have now with Rules. So Status would have Name, and Activity would look like:

  1. Name
  2. Status ID (to get the current status)
  3. The next Activity ID (assuming this is a linear workflow)
  4. GreenMax
  5. YellowMax

And on your main object, you would actually hold the activity ID instead of the status ID, because you can derive the status from the activity.

The other thing I would do is drop the Rules table. The only time that you would need a Rules table is if you have multiple statuses, multiple activities, and there are multiple relationships between these (for example, that both the New and Closed statuses both be able to perform the Assign Vehicle activity). Based on your example, this does not appear to be the case. It is possible that you actually do that many-to-many relationship between activities and statuses, if the relationship is not like your example. In that event, your Rules table looks okay, with the caveat that you don't need the Red limit.

Incidentally, I highly recommend that you put the actual workflow logic in a stored procedure, whether you keep the linear flow (like you have now) or move to a non-linear workflow model. You can use a combination of triggers and check constraints to force a workflow to operate in a particular manner, but it's a lot easier to understand if you just do it in a stored procedure: all of the code is in one place not in an easy-to-forget area like a trigger. Putting this in a stored procedure also abstracts it away from the application and business object layers, so they just need to call a method which says "go to the next workflow step" (and maybe pass in a detail to help you figure out which workflow step, in the event that this is not a linear process).

EDIT (post question update)

Based on your question update and comment, I'm okay with a three-table approach. I'm not positive that you need it, but this could simplify things at the application level.

At this point, what you have is a state machine, with a State (status) table and a Transition (activity) table. Your rule table then defines which transitions are valid for a particular state.

I would change the Rule table slightly, to look like this:

  1. Surrogate key
  2. Rule Name
  3. Starting State ID
  4. Transition (Activity ID)
  5. Ending State ID
  6. Green Limit
  7. Yellow Limit

There is one minor change: the position of the different keys. Your rule table now reads like a book: starting from one state, perform a transition and end at a different state (or the same one--you have an example in which the final state is the same as the initial state).

You would have a unique key constraint on starting state ID and activity ID, to guarantee that a particular transition from a particular state always ends in a single state.

On your vehicle table, I would put idRule instead of idStatus, to make it easy to tell which set of limits you need. You might also want a history table showing the vehicle ID, rule ID, and time added, so you can track what happened in the workflow.

This means that your "go to the next workflow step" stored procedure is actually pretty simple: given a vehicle ID and activity to perform, you can do a quick select against the Rule table to see if there is a valid transition from the vehicle's rule's state via the activity ID you get in the proc. If there is, perform that transition: update the vehicle table accordingly (setting idRule and LastUpdate); if not, return an error indicating that something must have gone wrong.

There is also no problem with having a table with two foreign keys back to another table. idCurrentState and idNextState can both be legitimate foreign keys back to State. You just need different names for the two foreign keys (e.g., FK_Rule_State_CurrentState and FK_Rule_State_NextState).

share|improve this answer
    
Thanks, I'm going to try to "digest" your whole answer,it looks totally right, but I need time to figure out what are you explaining, Once I get a new idea I'll update your answer as the correct one, thanks again. One more thing it seems to me that the "parent-child relationship with Activity" approach it's what I need, mainly because the requirements don't specify this (I'm assuming that can happen for example, go from State One to State Two, and also to State 3 (with diferent activities). –  Allende Nov 26 '12 at 15:36
    
Hi again @Kevin-Feasel,could you please extend the "parent-child relationship with Activity" approach, I edit my question and add the constrains and more samples on the table Rules, you were right about "If you have multiple statuses, multiple activities, and there are multiple relationships between these". Thanks again –  Allende Nov 26 '12 at 16:19
    
I've updated my answer to reflect your updated question. –  Kevin Feasel Nov 26 '12 at 17:19
    
Thanks @Kevin, It seems to work (not tested yet in a "real" application) but you certainly solve most of my doubts. I'm going to try model my "real application" first, after that I'll be back to make a last update for my post/question. And yes, you're right, I didn't mention nothing about "state machine" because every time I read about it, I got lost and didn't find nothing about how to model to a database, I try to understan/use the "Workflow Foundation" but it looks too complicated, well it's time to go on my own :) My regards ! –  Allende Nov 26 '12 at 19:02

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.