Date: Mon, 9 Feb 2004 10:00:54 -0700 (MST)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Peter Galbavy <peter.galbavy@knowtion.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: techniques for manual ordering of data ?
In-Reply-To: <007101c3ed67$cdab35f0$24e0a8c0@sonylaptop>
Message-ID: <Pine.LNX.4.33.0402091000310.23974-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.