Solved

WOrk flow process for the database

Posted on 2010-08-23
8
611 Views
Last Modified: 2013-12-20
I am working on the desigh of  the new database. VB 6 and ms access
This is kind of tracking database. Let say you have 10 fields:
Rec #
Name
Rate
Status of the record
and so on....
The main idea is here:
Involved two parties: Party A and Party B
As a member of the Party A you create the record and fill some fields. You can not completed the request (complete record) because you need something from another pary (Party B).
You mark the record some how and send it to Party B. party B opens the database and do their part and complete the task and then send to Party A. How to make the back and forth visible, may be anyone can give me some ideas, some examples., may be someone already did something like this? Really need your help
0
Comment
Question by:rfedorov
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 33505007
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.
0
 

Author Comment

by:rfedorov
ID: 33505792
thank you for your respond
I have that already, i think i need more...
to handle the status of the job, the dates...
0
 
LVL 22

Accepted Solution

by:
ambience earned 250 total points
ID: 33519438
I dont think you need to add two fields, thats not a scalable solution, what if later there is a need to add another party?
I would suggest that you
- add a Task table to your database. Each task has an assignee, status and date/time of when assigned as well as additional attributes like notes/comments by assigner.
- Add a task id foreign key to the record table, which holds the id of current task that needs to be performed on it. (Task has other details).
- Add a status filed to Record (or treat NULL task id as completed).
That way Party A can scan the task table and see which tasks are open for him/her to address and so on. Moreover a superuser can be given extra rights to change the status of task etc.
OPTIONAL
- If you add a previous_task_id field to the task then you can also track the history of actions performed on the table.
 
0
 
LVL 40

Assisted Solution

by:gurvinder372
gurvinder372 earned 250 total points
ID: 33557301
It seems to me that you are trying to put a workflow in place.

@ambience: Your solution is unique and handles the scenarios where
1) Any number of times the tasks can be added
2) Status is attached to the task, which will tell which party is working on the current task.

However, please allow me to ask a few questions
1) How does your solution takes care of
  a) More fields being added to the record
  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.
  c) If there is a approval/rejection cycle that includes another type of PartyC (says Admin)
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.
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?

There will other questions as well, like "Who owns the record at the end of the day?"

Hope it helps.

Thanks
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 22

Expert Comment

by:ambience
ID: 33565076
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 :)

0
 
LVL 40

Expert Comment

by:gurvinder372
ID: 33565438
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?

0
 
LVL 22

Expert Comment

by:ambience
ID: 33574105
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).
0
 

Author Closing Comment

by:rfedorov
ID: 33639438
Thank you very much
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

What is Waterfall Model? Waterfall model is the classic Software Development Life Cycle method practiced in software development process. As the name "waterfall" describes, this development is flowing downwards steadily like waterfall, i.e., procee…
Introduction This article discusses the Chain of Responsibility pattern, explaining What it is;Why it is; andHow it is At the end of this article, I hope you will be able to describe the use and benefits of Chain of Responsibility.  Backgrou…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now