Programmatically manage display order

[OogyWaWa]

I have a program I'm working on where I want to allow users to select and update the order of objects in relation to one another. The easiest way to think about it would be display order on a webpage - which object should come first. All the object details are stored in a DB, so it would be really nice if I could just order by a column and not have to worry about it anywhere else...

A few solutions I've found or come up with:
1) The old 'order_no' column
- An extra NUM column that gets a number assigned every time a field is updated/inserted
- This can be annoying/problematic and will eventually fail (requiring complete reordering of related objects every so often).
- Example, start with 10, the next item could be 20...then 30. Then someone updates the object associated with 30 to be before 20... ok.. make it 15... keep doing this though and you run out of numbers. You could resolve this by calculating the complete order on the fly (i.e. always determine what should be 1,2,3,etc and update) or do this at some interval (every hour go in and update 10->1, 15->2, 20->3, etc)... but seems like a lot of extra work...
2) Previous (or next) object relation
- The order column is a reference to the previous item in the list
- Ex) Object1 is first in the list and the order column is NULL. Object 2 is the 2nd in the list and it's order column has a reference to object1 in it...
- This is fine until you rearranged the order.. what if two objects have the same previous/next relation... how to resolve?

I'm sure there is some basic list programming principles i have missed, which is why i can't think of anything more elegant...

any ideas?

 

[Leros]

I had a similar problem a while back where I was dynamically composing UI elements. I needed a way to order things like buttons. I ended up going with your first solution, which isn't too painful most of the time since you'll leave large gaps in your number space.

If you run out of numbers (i.e. you need to place an item between 10 and 11), it's not too difficult to adjust everything. Something like: "update object_table set order_no = order_no + 1 where order_no > 10"

 

[beginner99]

Whats the problem with reordering everything? if you have order from 1-10 and the 7th element becomes the first update 1-6 by increasing number by 1 and 7 becomes one. done.

Or how many objects are we talking about? Since it seems it's for a gui it's most certainly less than 1000? So not a big deal in terms of DB performance and make the logic a lot simpler as you don't have to do a "complete reordering" at one point.

UPDATE [TABLENAME] SET order_column = order_column + 1 where order_column between [new order number] +1 AND [old order Number]

 

[Cerb]

I have a program I'm working on where I want to allow users to select and update the order of objects in relation to one another. The easiest way to think about it would be display order on a webpage - which object should come first. All the object details are stored in a DB, so it would be really nice if I could just order by a column and not have to worry about it anywhere else...

So, is the interface web-based? If so, how many rows are returned per sorted set?

1) The old 'order_no' column
- An extra NUM column that gets a number assigned every time a field is updated/inserted

2) Previous (or next) object relation

This is the same as #1, but less efficient. If you can use a souped-up array, don't fall back to a linked list.

any ideas?

Yes. An order table, with a key of (group_name, item, order_number). No coupling of presentation and data (this table is pure presentation info), easy to read, easy to debug, easy to rebuild on error, can easily handle many-many, one-many, many-one, and one-one relationships no sweat, and with most DBMSes, you could simply add an order by and not have to fuss with even knowing the order, unless it needs to be changed (which is a special event for any way of handling this issue).

OTOH, if the ordering is not part of the data--that is, this is something like a search result--then you should just add order columns in the query, and handle it in javascript, or load the whole thing over again, when an order change link is clicked.