WOrk flow process for the database

You can add 2 yes/no fields :

1 - Completed by A
2 - Completed by B


them you just set them to "yes" whenever any part finishes their job.

By the end you can create queries to select if you want to see only A completed or only B or both.

thank you for your respond
I have that already, i think i need more...
to handle the status of the job, the dates...

1) How does your solution takes care of
  a) More fields being added to the record
The dynamic nature of a record structure is not affected by the solution. Normally, the best answer to that is the meta-field pattern where you store attributes (record attribs) and their data in the database. The best example is the way shopping carts implement product features.
RecordItem
id (pk), task_id
RecordFields (one to many)
rec_id, field1_id (fk to FieldDesc), attrib1_data, status (Approved, rejected), assignee / ACL (who edits)
rec_id, field2_id, attrib2_data  
FieldDesc
id, name (e.g., 1, Comment, 2, Employee #, 3, Department)
Maybe also add ACL (access control list) here as a simple viewer_group, editor_group as two fields holding name of groups who can edit/view these fields.
This is just one way of doing it (in crude form, ofcourse you can refine it). One other way could be to store record data in XML (if searching within fields is not needed).
  b) If I want the task to be done by Users that belong to a TYPE of PartyA, and other tasks to be done by TYPE of PartyB users
Yes, you can extend the above to include security and other rules. For example, the task item will have an assignee field and it may be an ACL/groupname, in which case any member of the group can edit the record.
There are way to implement ACL (Access COntrol Lists) with records too, And if you see the FieldDesc structure above, you can even add an ACL there, so some fields can only be edited by users who belong to certain group.
Note that I am, for sake of simplicity, giving out example of only singular value items (like a groupname), and it should be pretty simple to adopt those to a one-to-many case.
  c) If there is a approval/rejection cycle that includes another type of PartyC (says Admin)
 Hmm... becoming interesting. Well one way is to add a WorkFlow table too, which is basically like a template of a work-flow. It has to be populated whenever a new workflow is available in the system (if your workflows are static in nature i.e. follow a strict pattern).
It serves to identify the number cycle of tasks, fields edtiable in each cycle, who can edit which fields (using ACL). It also holds ACL for who can cancel a workflow. Basically here you are going to implement a finite state machine in the database.
WorkFlowTemplate
id, name, acl_manager, ... , initial_item_id
-- WorkFlowItemTemplate
item_id, desc of cycle, required_fields (one to many list), next_item_id (next state in workflow), invalid_action (what to do if record is invalid, could be another state_id), acl_viewer (who gets to edit), acl_editor, acl_approver
-- WorkFlowRequiredFields
cycle_id, field_id (fk to FieldDesc)
Based upon these META-DATA tables you can create a workflow.
WorkFlow
id, template_id, current_task
WorkFlowTask
-- id, workflow_id, template_id (id to a workflowitem template), acls, status, .... (this is the task_id in record field)
So when the user submits the task, you can check the item template to check validity of record (by checking whether all required fields are ok). From that item template you will get id of next state, which can also give the next assignee of task.
This is basic structure and I may have missed out requirements, but I think it can be refined to include everything.

2) Which all fields will be assigned to a task? i.e. PartA users can enter records in field1, field2 etc. PartB can view and approve field1, and enter field3.
When a task is created, all required fields in its template will be added to it. The acl of the template determines the initial assignee of the task.

3) How do you define a Task? what does a task mean and how is it allocated to a Party with other parameters like expected time to complete?
I think that is already answered by now. The Templates determine structure of task, its cycle and other parameters. If you want you have have those as suggested defaults and not hard-coded facts. So if the person initiating a workflow can wish to extend the default completion time, he/she can. Upto you

There will other questions as well, like "Who owns the record at the end of the day?"
I guess thats simple to answer, every concerned one shares the record.
This has become quite a messy comment. I hope you can clean it up a bit when reading :)

Great. Especially loved the way you defined WorkFlowItemTemplate table.

Inline answering will make it even more messy :)...so will only comment on individual statements..will try not to quote out of context (but still if i do so, please let me know)

So let me first understand your solution correctly
1) A workflow needs to be established (using tables WorkFlowTemplate, WorkFlowItemTemplate, WorkFlowRequiredFields,  WorkFlow, WorkFlowtask  )
2) Once a workflow (with its tasks) is established, with every task completion you have to update the status of the current task in work flow table, and get the information of next task.

still following details are missing
1) <<There are way to implement ACL (Access Control Lists) with records too>>
What all information does ACL consists of? Also did you mean that "ACL with record fields too"? In that case, assuming that ACL will have the mapping of user roles to fields with a flag that will tell what kind of access a role have on this field, will those mappings be true for all kind of tasks. That is, if a particular task says that you can only cancel and cannot edit the 'number of days of leaves' (eg. for a person going for a vacation :) ) since it is already in approved state, will the mapping of this user type with edit button still hold true and active. Will the mappings of user role with fields/functions not change with task_id? I felt this info was missing.

2)
<<Basically here you are going to implement a finite state machine in the database.>>
There is a difference between a state-driven workflow and task-driven. When you defines a state, you are basically define an end result of a transition function (apart from the starting state). But when you define a task, you define a process with whatever resources available to it, and the way you have defined a workflow-task, it seems like you are basically mentioning what all resources (access permissions) are available to a task_id. Also how (with what inputs and by whom) you make a transition from one state to the other needs to be defined.
Not saying that this approach will not work, but how does the state of the resources vary with tasks and progression of tasks? Does a task mean that you can choose from a set of functions and proceed to next task? So if i have a choice of Editing and approving, will it always move on to the next task id, irrespective of which function i have choosen.

3) Current solution is little bit one-dimensional, as it doesn't explain
a) How does the solution takes care of scenario where multiple type of entities are in question? for example, if the 'country' attribute of 'Location' entity reads USA, i have to follow a different workflow (or steps) to take care of record of 'Record' entity. Also if the same entity is normalized in multiple tables.
b) How does the solution takes care of multiple records of same entity is in question? say if it is possible to edit/approve a batch of records. I think this should be possible within the scope of this solution, but i am not able to make out how.
c) How does cancellation/rollback/revert of task possible?

I am actually in a bit of confusion because the original post did not seem to hint such an elaborate, entirely data-driven approach. But the way its unfolding is interesting :) though it would definitely help if a complete picture of business requirements were available at this point.

If the intent is to have as much flexibility as possible and accomodate varying types of work-flows then I guess there are other ways to solve it that are more efficient and accomodating.

>> What all information does ACL consists of?

Thats a decision to take. It can be a simple list of group names like say "admin, user, manager" implying that all these have access to a specific role. Or it could be fairly elaborate with roles, base roles.

roles:
- role-name: admin
optional - inherits (list of roles): user, manager

acl
- id (unique id of an acl)
- roles-allowed (list of roles) = manager, *
optional - roles-denied (list of roles) = user
optional - order (enum) = allow, deny

>> Also did you mean that "ACL with record fields too"? Will the mappings of user role with fields/functions not change with task_id?

Thats the kind of cleaning up I was mentioning :) I kept answering your questions in a sequence.

I think it should change, basically the taskItem should determine the mappings for a role.

>> There is a difference between a state-driven workflow and task-driven

Im unable to spot a difference :( Are you reffering to a task-driven flow as one where the selection of ** events ** is free as such? Like edit and the state remains the sate and edit again etc.? One way or the other it is a state-machine, the way I see it.

If you look at SCXML for example, there is even a process to be executed when a ** transition ** is taken, so for example an "edited" event may trigger a "notify-by-email" process (on-event or on-exit) and then move to next state "pending-approval", or alternatively, the new state "pending-approval" may have "notify-by-email" as it's "on-entry" process.

>> how does the state of the resources vary with tasks and progression of tasks?

As I mentioned, I kept it simple and assumed that the only ** events ** are going to be completion of a workflowTask or invalid-completion. This event was to be implicitly triggered when a user submits a form and the "required-fields of the task are all ok." At that time, the workflow transitions to the next task (determined by the next_item of ItemTemplate). If the completion is invalid then another action can be chosen.

>> Does a task mean that you can choose from a set of functions and proceed to next task? So if i have a choice of Editing and approving, will it always move on to the next task id, irrespective of which function i have choosen.

Good question, actually the design did not elaborate how to deal with state transitions, though I did mention that probably an FSM may have to be implemented.

>> a) How does the solution takes care of scenario where multiple type of entities are in question? for example, if the 'country' attribute of 'Location' entity reads USA, i have to follow a different workflow (or steps) to take care of record of 'Record' entity. Also if the same entity is normalized in multiple tables.

It didnt (not a complete solution), even this comment may need further elaboration.

With every event and a state-transition there can be a ** condition ** that determines whether the transition is to be taken. So for example in SCXML format, the following fragment of state-machine could possibly solve it

<state id=s">
   <transition event="edit" cond="_Event.data.country==USA" target="us-edit"/>
   <transition event="edit" target="nonus-edit"/>
</state>
<state id="us-edit">
  <onentry>
    <assign location="user-form" expr="formUSA"/>
    <assign location="user-acl-edit" expr="foreigner"/>
    <assign location="entity-schema" expr="leaveApplicantUS"/>
  </onentry>
</state>
<state id="nonus-edit">
  <onentry>
    <assign location="user-form" expr="formOutsideUSA"/>
    <assign location="user-actions" expr="submit, cancel"/>
    <assign location="user-acl-submit" expr="employee"/>
    <assign location="user-acl-cancel" expr="employee, admin"/>
    <assign location="entity-schema" expr="leaveApplicant"/>
  </onentry>
  <transition event="edited" target="state-approval">
    <myapp:action function="send-notfication" params="to: manager"/>
  </transition>
</state>

Let me explain what that does. It defines two transitions for "Edit" event. One with a condition that checks that the entity's country attribute is USA. The on-entry for "us-edit" state, sets a few vars in the datamodel, the user-form I used to suggest a presentation to use, the acl-edit sets the security context, and the entity-schema I used to give schema to data captured in _Event.data.

Just trying to give an idea of how I was thinking of it.


b) How does the solution takes care of multiple records of same entity is in question? say if it is possible to edit/approve a batch of records. I think this should be possible within the scope of this solution, but i am not able to make out how.

In the absence of any information on where ideally would the workflow processor engine would be and how would it interact with data and front end its a bit difficult to say what would work best.

Can we use the "entity-schema" above to define structure of data a state is suppsed operatte upon? Maybe plain xml mapping can work here, like those used by Hibernate (ORM) to map attributes to SQL?

schema :

LeaveApplication {
 employee : Leave.person_id
 from : Leave.from_date
 to : Leave.to_date
}
leaveApplicant : LeaveApplication[1-10]

More details needed here ...

c) How does cancellation/rollback/revert of task possible?

Like a special event "cancelled"

<state id="s">
   <transition event="cancel" target="cancelled"/>
</state>

-----

As you may guess I have transitioned away a bit from what I had proposed earlier and moved to a more SCXML like FSM powering the workflow, which is partly because I still dont think the business scope/objectives are clear and therefore lot of speculation comes into play.

SCXML I think it a powerful beast capable of powering advanced workflows, for example it can even have multiple active states (like parallel flows).

Thank you very much