how to go about designing database for workflow?

Tangara G

Hi experts,

I hope I can get some advice here cos I am really lost how to go about designing work flow in a database.

Basically I have a form which will be saved as a draft. The user can retrieve the draft and then can void it or submit it to the Mgr for approval.

After the Mgr approved, it will go to the Warehouse Mgr approval before it goes to the Security for approval.

And I need to keep track of all these status as in where is the form now, whether it is pending approval or has been approved or now at which Mgr's hand etc.

Can someone tell me how to do a database like that, using Stored Procedure. Tks.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tangara G9y ago
Hi Nitin,

Could you advise what is a workflow template ?

I googled on the net but found none.

I am now at the verge of collapsing since I only have 1 month to do the project :(

And there is still the email part that I really have no idea how to do.
But, first, please help me on this. Tks.
Tangara G9y ago
Hi Nitin,

I like your idea below:

Then design the transaction table (t1) considering only one action. Typically, even if just write a trigger on a transaction table and keep on logging each record change on status change in a separate table (t2), your workflow gets built.

However, how do I write a trigger on a transaction table and link this to the update method in my aspx.cs?

Actually, I have a reference code which took me very long to even understand abit.

However, it is very strange cos this guy he uses a updateXYZ method like this:
private void UpdateXYZ(string action, string formId, int status) {
eBiz.UpdateXYZ(action, formId, Session["SGID"].ToString(), lblName.Text, Session["MgrEmail"].ToString(), status)

The thing that puzzle me is that he uses the method to call the logic in eBiz.UpdateXYZ and this will call the DAO that contains all the Stored Procedure.

And then he uses 3 scalar function which will feed the status to the Webflow and updateWholeTable in the database.

I find it really complicated and have yet to test out the method cos I am really new in asp.net.

Do you have a easier method to do things ? Or can you be more specific regarding your method ?

Hope to hear from you soon.
Tangara G9y ago
Hi Nitin, How r u ? Tks for coming to my rescue. Right now, i am still in de thinking stage n i cant copy n paste de code for u to see, cos this internship compsny is very strict n i am accessing this via my hp. I would like to ask u on weekends. Tks.
Nitin Sontakke9y ago
If you also plan to have a workflow template, keep it's id (workflowTemplateId) and keep the workflowTemplateId in actual (transactional) workflow table. Which has it's own id which is worldflowId.
Nitin Sontakke9y ago
This all depends on how complex implementation you want.

First start with masters. I am in big favor of masters for each and every small enum like values, for example, workflow stages like 'initiated', 'pending approval from a', 'pending approval from b', etc. and all such things. For best performance, always use ids in table and not varchar columns.

Then design the transaction table (t1) considering only one action. Typically, even if just write a trigger on a transaction table and keep on logging each record change on status change in a separate table (t2), your workflow gets built. You then always combine the t1 and union all t2 get the over all picture. May be write a view for this. The record in t1 is your current state of the workflow.

Hope it helps to begin with. More question(s), please do not hesitate to ask.