Visually order large data sets

Question

I have an interesting problem:

I'm building a web based image gallery program that right off the bat is going to include almost 7,000 images.

So, here's my question: What would be an optimal way to build an ordering system?

For example, if the admin wants to have the 4,984 image land in the 3,223 position (I work with some anal people who I just know are going to want to do just that :).

Since this is a web based system I REEEEAAAALLLLYYYY don't want to have to update all 7,000 db entries every time an entry is repositioned but that's the most accurate way I can think of to do this.

So, does anyone have a better way?

Answer 1

Are all images interchangeable? (Are they the same size?) The simplest I can think of is, for each image store metadata, id, x position, y position, and maybe the index (3,223 in your example). Once you reposition an image, change the relevant fields in the image's DB record. Or maybe I am missing something.

Answer 2

You have several ways of ordering a set in the database:

1. Adding an row number to each row, which would require you to update all entries when one is moved.

2. Adding an AfterRowId to each row -- in this case you will have to update the row that was after the moved one and then one that will be after it.

As I understand, first way is something you do not want to do. The second way is harder to select from if the database does not support recursive expressions (CTE or other).

There is another solution, but it is more risky -- you can make row number a double/decimal value, and when moving a row between row 2 and row 3, set its row number to 2.5. This will fail after the precision limit is reached, so you will have to renumber the database periodically or detect the precision loss.

Answer 3

I would suggest an solution similar to Andrey. Add an orderby column of type longint and initialize that column with values of multiple of 1000 (1,1000,2000,3000 etc). Then when you need to change the order of a single image, you set that images orderby column to the (orderby of the row after + the orderby of the row before) / 2.

the pseudosqlcode would be something like this:

moveImageTo(int imageIdToMove, int imageIdMoveTo) 
{    
    int orderIdMoveTo = (select orderby from images where id = imageIdMoveTo);
    update images set orderby = (orderIdMoveTo +
        (select top 1 orderby from images where orderby > orderIdMoveTo order by orderby)
        ) / 2 where id = imageIdToMove;  
}

Also as Andrey pointed out you will periodically have to run through all the images and reinitialize the orderby column. I would create a stored procedure for doing that, pseudosqlcode would be something like this:

alter table images create temp_orderbycolumn int null;
create updatecursor that initializes temp_orderbycolumn with 1,1000,2000 etc (this cursor must run in the order of the orderby column)
update images set orderby = temp_orderbycolumn;
alter table images drop temp_orderbycolumn;

PS: I enjoyed the description of the people you are working with :D

Answer 4

Add meta data to describe the image in different ways. Make the meta data extendable so you can add new ways of presenting the images. For instance you could add different orderId's for different purposes. Or a weight coeficient to determine if the image will be high or low in the list.