Maintaining the ranking order in continuously updating database

Question

Suppose a database table contains information about a search term in each row. The table has an integer "rank" column to dictate the order in which the search-terms are to be presented on a web site.

How do I best implement and maintain this rank order?

The problem I want to avoid is having the term numbered 1,2,3,4,..,100 and when for example, rank 50 suddenly becomes interesting it gets its new rank set to 1 and then all terms between them must have their sort number increased by one.

Sure, setting initial sort numbers to 100, 200, 300, 400 etc. leaves some space for moving around but at some point it will break.

Another approach can be spacing the ranks with some fixed offset but it may also get break at some time in real world scenario.

so is there any other different approach to handle this.... (it may be relevant to how search engines maintains the ranking and reordering but i can not find any luck)

Answer 1

How about this idea. Like queue.

Create additional table Task(id, new_rank, ...). Create trigger for main table on insert.

When new row is coming to insert in main table then do:

1. Disallow insert new row in man table;
2. Insert new row in Task table.

Run for example every hour stored procedure which:

1. Select all new rows from Task table in cursor;

2. In loop:

   2.1. Run increase (prepare) rank in main table (to be able to insert new row rank value);

   2.2. Insert new row in man table;

   2.3. Delete row from Task.

Maybe you need to do 2.1-2.3 items in transaction.