Maintaining sort order of database table rows
- by Lox
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.
