Insert row between rows?

[vigour]

I'm building a picture database. All filenames is like this:

XXX 00001.jpg
XXX 00002.jpg

And so on...

The pictures is sorted (when showed to the user) like this:

XXX 00001
XXX 00002

And so on...

Now to my question. I want the user to be able to insert a picture between two other pictures. Let's pretend that it's 3000 pictures in the database from XXX 00001 to XXX 03000. The user want to insert a picture after between picture XXX 00100 and XXX 00101. How can I do this?

An earlier solution was to rename the new pictures to XXX 00101B, XXX 00101C and so on but I do not want to do it that way. I don't want to add anything at the end of the name.

[Bjom]

You mix two things into one column: name and sort order.

Once you named the pictures that way, you're stuck, aren't you? If 100 and 101 are already taken you cannot add a picture in between without creating a mess, changing names at a later point is not the best idea - it can lead to confusion both in your app and in the user.

If you are only updating on a very very small scale you can:

Name the pictures with larger increments, say 00010 - 30000. That leaves at least some space.

The better way is to have names w/o any relation to the sorting order. Add a column where you put that info. Maybe in the same style as above. You can rearrange, readjust, reallocate, do whatever to the column without affecting names, that somebody might have got used to...

Bjom