Priority list of tasks stored in a database

data structuresdatabase-designtheory

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.

Best Answer

  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.
Related Topic