Priority list of tasks stored in a database

Question

I am trying to think of the best way to do the following:

I have a list of tasks stored in the database. A task has a priority assigned to it. You can change the priority of a task to reorder the order they should be carried out.

I am thinking of something very similar to Pivotal Tracker.

So imagine we had the following:

1 Task A
2 Task B
3 Task C
4 Task D
5 Task E

We decide that E is now the most important task

1 Task E
2 Task A
3 Task B
4 Task C
5 Task D

I need to update all 5 tasks to give them a new priority.

If Task B then becomes more important then A I would I would have

1 Task E
2 Task B
3 Task A
4 Task C
5 Task D

I need to update Task B and A only.

What ways would go about structuring this in a DB? I imagine that you would have a differnt projects stored in the same table that would have there own weight.

Would it be better to point a Task that takes place after it (a bit like a link list).

This is just a brain dump really. Just was wondering how you would go about implementing something like this.

Answer 1

1. It looks like you are looking for a priority queue. You probably shouldn't re-calculte priority numbers for tasks, you should just calculate a fixed value for them. If you want task E to be more important, decrease it's value.
2. You are essentially talking about relations. B should be more important than A. E should be the most important task, etc.. It sounds like a tree structure, and you can store that in an RDBMS with parent links.

Answer 2

If you use double floating point numbers to indicate priority you do not need to re-order:

1.00 Task A
2.00 Task B
3.00 Task C
4.00 Task D
5.00 Task E

If you want to place task E between A and B then:-

  E.priority = A.priority + ((B.priority - A.priority) / 2)

So now you have:

1.00 Task A
1.50 Task E
2.00 Task B
3.00 Task C
4.00 Task D

If you wanted to insert D between E and B then simply set its priority to 1.75. Given the approximately 18 decimal digits in a floating point number (1.75 is really 1.7500000000000000) you should have a worst case of 53 consecutive inserts before:

 A.priority + ((B.priority - A.priority) / 2) = B.priority

And before anyone complains about the overhead of using doubles vs. integers, its just a few hardware instructions, compared with the processing and I/O overhead of reordering the list in the database which would be several orders of magnitude greater.

Answer 3

We did this very thing you are talking about. We did this by using one stored procedure that reordered the list of items. Each item in the list had a unique id and a sort order number.

For Example:

TaskId int identity(1,1),
Task varchar(50),
SortOrder int

The stored procedure that reorderd the items takes two input parameters:

@TaskId int,
@NewSortOrder int

We used a temp table to store the items in the new order:

CREATE TABLE #Tasks
(
RowId int identity(1,1),
TaskId int
)

We used three select statements to get them into the new order:

-- Step 1
INSERT INTO #Tasks
SELECT TaskId FROM tblTasks
WHERE SortOrder < @NewSortOrder
ORDER BY SortOrder

--Step 2
INSERT INTO #Tasks
VALUES(@TaskId)

--Step 3
INSERT INTO #Tasks
SELECT TaskId FROM tblTasks
WHERE SortOrder >= @NewSortOrder
ORDER BY SortOrder

We then updated the base table (tblTasks) with the new sort order which is actually the RowId identity column of the temp table:

-- Update Base Table
UPDATE tblTasks
SET SortOrder = t2.RowId
FROM tblTasks t1
INNER JOIN #Tasks t2
ON t1.TaskId = t2.TaskId

This works like a champ every time.

Answer 4

I haven't thought this through yet..... But why not just allow decimals so you can stuff things between others without updating everything?

You could squish something between 1 and 2 with the value of 1.5.

I'd also avoid min and max values. Allow numbers to roll into the negatives if they priority comes before whatever is currently 0.

You may consider having a "human display" priority seperate from the internal "ordering" priorities to avoid showing wierd decimals and negative values.

Answer 5

It's very reasonable to implement a Linked List and its operations in a RDBMS. Just replace array and reference manipulations with SQL queries. However, I'm not sure if this is really the most efficient way to go as some simple operationw will require many SQL queries

For the task table, you add a column "next_task" and "prev_task" which are foreign keys to the id column of the same table (assuming that a "-1" is equivalent to NULL)

Return the task with the highest_priority(): SQL query which returns the task with prev_task = -1

E is the most important task: SQL query which changes the next_task of E to the ID of the task with the highest priority. And changes prev_task of E to -1...

This and other operations like putting E before A, or printing an ordered list of tasks will require many more SQL queries which should be all atomic (unless you're able to optimize). It is a good exercise but maybe not the most efficient way of doing it.

Answer 6

Another approach to the priority problem would be to specify what item is more important than the item. In an HR application, this would be like saying who an employee's manager is.

ID  Name           ParentPriority
1   TopPriority    NULL
2   Medium         1
3   Low            2
4   AnotherMedium  1
5   Less than 2    2

Then read this http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/ to make a query that gives priority levels.

ID  Name           ParentPriority  PriorityLevel
1   TopPriority    NULL            1
2   Medium         1               2
3   Low            2               3
4   AnotherMedium  1               2
5   Less than 2    2               3

I think this is a simpler user experience in setting priorities, but it does allow for multiple priorities of the same level.

Answer 7

One simple way would be to start off with something like this:

100 Task A
200 Task B
300 Task C
400 Task D
500 Task E

Then, to move "Task E" between Task A and Task B, say, you'd just set "Task E"'s priority to something half-way between Task A and Task B (i.e. "150" in this case).

Of course, if you constantly rearrange priorities, you'll eventually run into a problem whereby two adjacent tasks have no "gap" to insert new entries. But when that happens, you can simply "reset" all of the priorities in one go back to 100, 200, 300, etc.

Answer 8

I'm no database guru, so I solved this the most logical way I could in Access 2010. I have a "Priority" field that is a numerical field. Then I have an event for this field.

The event is an after update event for field "Priority" that triggers an Update Query "qryPriority" to add 1 to the priority number of all other records that have a priority greater than or equal to the priority number just entered.

Here is the event VB code and Update Query SQL:

"Priority" Event VB Code:

Private Sub Priority_AfterUpdate()
Me.Refresh
If Priority > 0 Then
DoCmd.OpenQuery ("qryPriority")
End If
Me.Refresh
Priority = Priority - 1
End Sub

"qryPriority" Update Query SQL:

UPDATE YOURTABLENAME SET YOURTABLENAME.Priority = [Priority]+1
WHERE (((YOURTABLENAME.Priority)>=[Forms]![YOURFORMNAME]![Priority]));