Re: Best practices to WorkFlow design?

On Jul 10, 2009, at 3:00 PM, Andre Lopes wrote:

> Hi,
>
> I will be developing a WorkFlow Application, but I don't know the
> best practices on how to design a WorkFlow on a Database.
>
> Can you give me some clues? Books, links on the Internet, etc...

It depends on how far you want to take that.

For many purposes it's sufficient to create a status table for every
table that needs to track workflow. A typical status table contains a
foreign key to its base table, a status (or an FK to a table with
possible statuses), a timestamp and probably who was responsible for
that status.
From experience I advise to also add an (initially deferred) FK from
the base table to the current status, updated through a trigger.
Otherwise you'll have trouble determining the current status of
objects - it's possible, but the queries needed for that are complex
and not likely to perform very well.

You can also move the entire workflow engine into the database. A
former colleague did that for Oracle: http://plflow.sourceforge.net/
His original design was much simpler than what's described there, but
the basic components are in there somewhere.

If you want to go somewhere in between, you need at least some way to
define workflow tasks and relations (how to go from one task to the
next) and of which workflow they're part. You will also need tables
mapping to those tasks and tables logging how you got there (when it
happened, who did it and sometimes under what conditions).

As workflow tables tend to have circular dependencies I suggest you
use 'on delete cascade' for foreign keys referring to a dependent
record, or you'll be sweating a bit if you ever want to delete an
active workflow process - which is likely to happen more than once
while you're still developing.

If you intend to handle conditions in the database, I suggest handling
the expression evaluation needed for that in a language other than pl/
pgsql, it will be much easier that way.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a585cce10131750817515!