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.