1

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)

Jitendra
  • 1,107
  • 2
  • 12
  • 22
  • It cannot be considered as a duplication, this question is about the relative independent ranks of row updating frequently in the large database. All the data in this rank field are completely independent and can be resorted as system want(in case search algorithm). ** NO OTHER COLUMN PLAYS ANY ROLE ON INDEXING/RANKING THE RESULTS ** – Jitendra Jan 14 '14 at 18:29
  • @Jitendra do you already solve this problem? – DoNotArrestMe Jan 21 '14 at 12:32

1 Answers1

0

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.

DoNotArrestMe
  • 1,285
  • 1
  • 9
  • 20
  • It may not work properly if data is multiple GB. I dont know why but some one fool edited my question for totally different answer.. I hate this non aprooval thing about Stackoverflow this just pisses me off that someone who do not know about my problem walks changes my question and goes away!!! – Jitendra Jan 22 '14 at 09:59
  • @Jitendra You can see who edited your message here [http://stackoverflow.com/posts/20937158/revisions](http://stackoverflow.com/posts/20937158/revisions). Also you can contact support here [http://stackoverflow.com/contact](http://stackoverflow.com/contact). – DoNotArrestMe Jan 22 '14 at 10:12
  • thank you... and thank you for the answer. my original question was in the context of the search engine ranking where millions of the records are to be altered if rank change is handled by recursive updates... – Jitendra Jan 22 '14 at 10:29