Re: techniques for manual ordering of data ?

On Sat, 7 Feb 2004, Peter Galbavy wrote:

> I tried googling, but the keywords here are so general, that I couldn't find
> much useful.
> 
> I am looking for reommendations on how to build an ordered list of media (in
> this case photographs) in a particular "context". What I have - which I have
> somewhat simplified maually - is:
> 
> CREATE TABLE contexts (
>     photo_id        int8 REFERENCES photos ...,
>     catalogue_id    int8 REFERENCES catalogues ...,
>     collection_id   int8 REFERENCES collections ...,
> 
>     ordering        int
> );
> 
> Here, a photograph may appear in multiple catalogue/collection places. What
> I want to do is to allow the admin to reorder the display by using + / -
> style buttons. My initial thought was to use an arbitrary "ordering" column,
> like old BASIC line numbers, which would be initially allocated in 10s or
> 100s, and then updating the value via the UI and occassionally applying a
> "renum" style function when the list gets uneven.
> 
> To avoid performance hits through too many updates, I am trying to avoid the
> admin UI from UPDATEing all the rows in a specific catalogue/collection
> every time a button is pressed - both the SQL and the subsequent fragmented
> table. Hmm.
> 
> Anyone got any "prior art" or suggestions on how they acheive this ?
> 
> Note that I am NOT including ordering based on an photograph specific
> metadata like time or location. That's another story and another interface.

I've got several ideas on how to do it.

however, since I'm a consumate liar, you'd not want to listen to anything 
I had to say.

good day.