Work Queue Design Question

Hi,
Although this is more of a program design question, it's in Coldfusion so we'll be talking the same "language" so to speak.

I have a "work queue" shared by several administrators to review community data added to a "company" table (for example).

When admin1 selects revew task1, it should no longer be available to the other administrators.

OK.  So I can update a flag on the work_queue table.  If admin1 closes the browser and leaves for the day, do I keep that task still assigned to admin1 or put it back in the pool?  I'm not sure how I would even know how to detect that unless admin1 indicated he/she was done with the review or releasing it back to the pool.

Am I on the right track?  I have never worked with a queue type application.

Thanks in advance,
hefterr
LVL 1
hefterrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
> So I can update a flag on the work_queue table.  If admin1 closes the browser and leaves for the day, do I keep that task still assigned to admin1 or put it back in the pool?

This part is a question for the business - it's a fundamental requirement that they should provide you.   Once they do that, I'm sure we can help code it however they need it to be done.

There are a bunch of scenarios, perhaps the assignee takes ownership of the task, he could set a flag or something that says he owns it but it may be a soft-close where the user will test the solution and get back to him the next day... therefore, no need to re-assign.

In another scenario, the case could start in the assignee's queue, but if he doesn't pick it up or take ownership of within X minutes, it goes to the open queue.  To do this, you can run a scheduled task ever few minutes to change the status of a task given a set of criteria...

So, I guess we should start with a set of the user's requirements...   what is the desired flow?



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
I agree. It is up to each implementation. To help you make the decision, put the focus on what is important in the workflow as indicated above. For example, most incident management applications I have used treats a work (team) queue as just that. Items on that queue remain in the queue. Individuals can just assign individual tasks to themselves or request others take it over. So it would be a mix of both. Admin1 would own that task until s/he explicit notes they are no longer able to work on it and request another in the queue take it over. At the same time, the task does not move from the queue. Therefore, all the team members can see that Admin1 has been sitting on task for a week. If they know for example Admin1 went on vacation and will not be back and task is due tomorrow, another member can take over completion of the task. Therefore, teams have multiple views. You have a team queue and an individual queue. They work in tandem.

So from a database perspective, you have a team assignment table: TaskID, TeamID
Then an individual assignment table: TaskID, TechID
Or I guess, you can have: TaskID, TeamID, TechID << When TechID is NULL, this is an unassigned task. When it gets assigned to a TechID, queries for Tasks by TechID gives you Tech queue. Which design is better is based on if a Tech can be on more than one work queue/team, etc. I am currently working in a project management web application that allows multiple assignments to a single task/issue; therefore, the separate associative table for TaskID and AssigneeID is more beneficial.

The benefit of all of these is that there is one Task table (global work queue), so just as you look at all team work queue tasks despite who moved it to an individual queue you can view all open tasks for all work queues.

Hope that helps!
0
Kevin CrossChief Technology OfficerCommented:
As a note, when I worked in ITIL packages for incident and task management, only members of a team/work queue could assign to individuals; therefore, that is how I normally think of this. gdemaria's points on individual assignee is valid. We too had scheduled jobs that performed escalation. For example, alert the assignee, then assigned group, then group manager, then overlord *smile*, etc.

Going back to what I know, though, the queue is based on team. We used on-call rotations, so the alert/page on task initially goes to the person on-call; however, anyone on team could see the open and pending tasks in the queue. The on-call individual usually handled the tasks for that week, but could assign to someone else if for example in the case of an Intel Systems  queue you have one individual that is your SAN Administrator -- disk related issues may go to him. But then again, that is where the idea of multiple queues comes in. We had a team and then within that team subsets expertise, so you can have a Server Group, then segmentation by types of server or technologies like SAN, backup, etc.

So you do not have to make automation calls to move items from queue to queue, but rather send escalations that a task with a specific due date or service-level agreement to customer is not being satisfied. The managers of the queue can work out the movement. So from application development, you keep it simple and don't have to code around every exception, i.e., task is not overdue but everyone in the group knows Bob won't be in to work today, so tasks in his queue need reassigning.

Kevin
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

hefterrAuthor Commented:
To all,
I agree that I have to ge the business flow nailed down first.  But i am pretty suer that there will be multiple administrators looking to take the next review task off the top of the queue.  How (or who) relinquishes or reassignes a task is a question I have to flush out tomorrow.

The case where admin1 is on vacation will require a way to reassign the task (or mark it unassigned).  Perhaps this is a higher security level where all tasks are displayed (team view) and a super-admin can release the task back to the pool.

I'll get back to you tomorrow after I talk with my boss.

Good ideas to work from!!!!  Thanks.
0
Kevin CrossChief Technology OfficerCommented:
Good luck!

"Perhaps this is a higher security level where all tasks are displayed (team view) and a super-admin can release the task back to the pool." < Yes, that likely works. As I said, in most tools I have used anyone on the team can reassign to individuals on the team. When assigning outside the team, you assign to top-level team queue. A super-admin, better business term than overlord, can then have the ability to handle separation of employment, vacation, or other exceptional scenarios where you do not have a member of a team available to move a task that needs to be.

Let us know what you work out with the boss.
0
gdemariaCommented:
Kevin brings up some really good points and options.

When talking to the boss, try to work through all the scenarios from what happens when a new task gets entered, task ownership, task sharing, reassignment, escalation, lack of response, etc...

Other systems I have seen have a task Owner and an assignee, the owner is like the team supervisor who is responsible for the queue and he/she has the ability to move things around.   So once you have this all flushed out, let us know!
0
hefterrAuthor Commented:
You guys always help me seem smarter than I am :)  Thanks.
0
hefterrAuthor Commented:
To all,
I spoke with my boss and for the first phase we are going with a very simplistic approach (we are a small company with just a few programmers).

-  There will be a "community" task page where any admin can grab a task.
-  The task is owned solely by that admin unless it is put back into the pool by the supervisor.
-  Admins has their own task page of work assigned to them
-  Points are earned at the field/level for the user that added the data.
-  Points are earned by an admin for adding missing data or overriding user data.
-  Points are earned by an admin for completing a review.

So I need to keep an activity table at the column level :
-  Task ID
-  Original user ID
-  Admin ID
-  Field name
-  Old value
-  new value
-  fields Point (admin)
-  field points (user)
-  Approval status
-  Date approved

Perhaps a summary table to display the tasks in progress or awaiting assignment:
-  Task ID
-  Associated User
-  Admin Assigned
-  Date Assigned
-  Action (original or update)
-  Total points

Something like that.  A daily task would send emails if work was not completed in a timely mater.

My boss was even willing to go without a "reassignment" option for phase 1.  If there was a problem with an admin, someone would log in with their ID (or perhaps the boss ID has access to everything).

Comments?
0
Kevin CrossChief Technology OfficerCommented:
Couple thoughts. Am I reading correctly that you will have a Task table and the tables shown are additional, associative type tables? Otherwise, you will need to consider some normalization.

Anyway, on the activity table, the structure leads me to believe you are going to be maintaining one row per field changed? Will you ever have more than one field changed in one shot, i.e., one change consisting of multiple fields updated. Based on your database platform, you may be able to store this as one column using XML data type to reflect changes.

By summary table, do you mean views or an actual table?

Anyway, sounds like you have a better handle on scope now. That should help.
0
hefterrAuthor Commented:
@mwvisa1,
Am I reading correctly that you will have a Task table and the tables shown are additional, associative type tables? Otherwise, you will need to consider some normalization.
Not sure what you are asking?

Will you ever have more than one field changed in one shot, i.e., one change consisting of multiple fields updated.
Yes, many fields can be changed in a "session" by a user.

Based on your database platform, you may be able to store this as one column using XML data type to reflect changes.
I am not familiar with this approach.  Is their a post you can point me to as an example?  I am using SQL Server 2005.

By summary table, do you mean views or an actual table?
I meant an actual table.  But I think I will start without one and see how the SQL performs to create a task list from the "activity/field" table.

Thanks so much for you help
0
hefterrAuthor Commented:
Thanks to both of you.  You have been a big help!
0
Kevin CrossChief Technology OfficerCommented:
I was just making sure in an effort to audit changes, you were not ending up with your live Task table having multiple rows per task for every modification you have had. Audit data is usually separate from live transactional table.

Tim shows a nice example of this here:
http://www.techrepublic.com/article/audit-data-using-sql-server-2005s-columnsupdated-function/6175865#
You can modify his example by JOIN'ing the DELETED table in with INSERTED as DELETED will hold the Old value and INSERTED the new. The basic idea is that you can build your own XML and store it in one column using SQL 2005's new XML data type. I am not sure if the newer versions of CF has a cfsqltype that directly maps to XML data type in SQL Server; however, you can always pass it as a VARCHAR that happens to be well-formed XML from CF to the database if any of the changes are happening on the web site of things.

Hope that helps!
0
hefterrAuthor Commented:
@mwvisa1 :
 you were not ending up with your live Task table having multiple rows per task for every modification you have had



I may be way off on this but I'm using an "activity/upate" table as BOTH an transaction table and an audit table.  If a user modifies 5 fields in an organization, They all have to be reviewed.  I was going to create a row for each field indicating the user, status, column name value, reviewer, points etc.

 The admin will need to see these fields highlighted in the review process.  They also carry a point values to be attributed to the user who entered the 5 fields.  The admin can accept or reject any of the updated fields.

In short, it is really designed more as a transaction table but it can serve as an audit table as it has all the details that occured.

I was considering a de-normalized "activity summary table" to make it quick to display work tasks for the admin instead of searching through the activity table using "group by" or "distinct" - but I will try it without it first.

I haven't looked into the details of the XML solution yet.  If I understand it correctly, the 5 modified fields could be stored as one column in a single row instead of having 5 rows.  I will look into that.

Not sure if I answeed your question?

hefterr
0
Kevin CrossChief Technology OfficerCommented:
Yes, that makes sense. The XML method is to  consolidate changes to one row for audit. If you need approvals by field that changed, so that may be a good way to go.
0
gdemariaCommented:
My 2 cents .. I would store each change of a field in a record.  That will give you maximum flexibility.  For example, if you want to show the change history for one field, easy to do..  if you want to show the values of all fields as of a particular date, easy to do.   You can assign points for each field individually and sum them easily by grouping the records.  You can associate records to the person who changed them, who owns them, etc...

I think one field change per record would give you the most flexibility.

But I have to say, I am also not familiar with the XML approach, so I can't say that's not a good approach - I'm just saying I like this approach..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.