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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
"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.
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!
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!
ASKER
You guys always help me seem smarter than I am :) Thanks.
ASKER
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?
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?
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.
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.
ASKER
@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
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
ASKER
Thanks to both of you. You have been a big help!
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!
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!
ASKER
@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
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
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.
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..
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..
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