Alex Rivera | Logout

Maintaining sort order of database table rows

Asked 2008-12-29T19:30:58.640
13

Say I have at database table containing information about a news article in each row. The table has an integer "sort" column to dictate the order in which the articles are to be presented on a web site. How do I best implement and maintain this sort order.

The problem I want to avoid is having the the articles numbered 1,2,3,4,..,100 and when article number 50 suddenly becomes interesting it gets its sort number set to 1 and then all articles 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.

Is there a correct way to do this, maybe a completely different approach?

Added-1:

All article titles are shown in a list linking to the contents, so yes all sorted items are show at once.

Added-2:

An item is not necessarily moved to the top of the list; any item can be placed anywhere in the ordered list.

database-design
Edit
Report

1 Answer

0

I'd go with the answer most people are saying, namely that you should have a separate column in your table with the record's sort/interest value. If you want to factor in some sort of time argument while still remaining robust, you might want to try and incorporate a data "view" for a given time period.

I.e. every night at midnight you create a table view of your main table but only create it using articles from the past 2 or 3 days, then maintain the sort/interest counts strictly on that table. If someone is selecting an older article not in that table, (say from a week ago) you can always insert it into the current view. This way you can get an effect of age-off.

The whole point would be to maintain small indices on the table where inserts and updates of sorting/interest values should remain relatively fast. You should only have to pay for complicated joins once up front during the view creation, then you could keep it flat to optimize for excessive reads. What's the max you would have in the view, a couple hundred, couple thousand? Beats trying to sort a hundred thousand every time someone makes a request.

answered 2008-12-29T22:08:56.747

Your Answer