What methods are there for storing the order of items in a database?

Question

I'm creating a portfolio website that has galleries that contain images. I want the user of this portfolio to be able to order the images within a gallery. The problem itself is fairly simple I'm just struggling with deciding on a solution to implement.

There are 2 solutions I've thought of so far:

1. Simply adding an order column (or priority?) and then querying with an ORDER BY clause on that column. The disadvantage of this being that to change the order of a single image I'd have to update every single image in the gallery.
2. The second method would be to add 2 nullable columns next and previous that simply store the ID of the next and previous image. This would then mean there would be less data to update when the order was changed; however, it would be much more complex to set up and I'm not entirely sure how I'd actually implement it.

Extra options would be great.

• Are those options viable?
• Are there better options?
• How could / should they be implemented?

The current structure of the two tables in question is the following:

mysql> desc Gallery;

+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| title        | varchar(255)     | NO   |     | NULL              |                             |
| subtitle     | varchar(255)     | NO   |     | NULL              |                             |
| description  | varchar(5000)    | NO   |     | NULL              |                             |
| date         | datetime         | NO   |     | NULL              |                             |
| isActive     | tinyint(1)       | NO   |     | NULL              |                             |
| lastModified | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------------+------+-----+-------------------+-----------------------------+

mysql> desc Image;
+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| galleryId    | int(10) unsigned | NO   | MUL | NULL              |                             |
| description  | varchar(250)     | YES  |     | NULL              |                             |
| path         | varchar(250)     | NO   |     | NULL              |                             |
| lastModified | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------------+------+-----+-------------------+-----------------------------+

Currently there is no implementation of ordering in any form.

Answer 1

As bart2puck has said and I stated in the question, option 1 is a little bit ugly; it is however the option I have chosen to go with to simplify the solution all round.

I have added a column (displayOrder int UNSIGNED) to the Image table after path. When I want to re-order a row in the table I simply swap rows around. So, if I have 3 rows:

mysql> SELECT id, galleryId, description, displayOrder FROM Image ORDER BY displayOrder;
+-----+-----------+----------------------------------+--------------+
| id  | galleryId | description                      | displayOrder |
+-----+-----------+----------------------------------+--------------+
| 271 |        20 | NULL                             |            1 |
| 270 |        20 | Tracks leading into the ocean... |            2 |
| 278 |        20 | NULL                             |            3 |
+-----+-----------+----------------------------------+--------------+
3 rows in set (0.00 sec)

If I want to re-order row 278 to appear second rather than third, I'll simply swap it with the second by doing the following:

UPDATE Image SET displayOrder =   
  CASE displayOrder     
    WHEN 2 THEN 3
    WHEN 3 THEN 2
  END 
WHERE galleryId = 20 
AND displayOrder BETWEEN 2 AND 3;

Resulting in:

mysql> SELECT id, galleryId, description, displayOrder FROM Image ORDER BY displayOrder;
+-----+-----------+----------------------------------+--------------+
| id  | galleryId | description                      | displayOrder |
+-----+-----------+----------------------------------+--------------+
| 271 |        20 | NULL                             |            1 |
| 278 |        20 | NULL                             |            2 |
| 270 |        20 | Tracks leading into the ocean... |            3 |
+-----+-----------+----------------------------------+--------------+
3 rows in set (0.00 sec)

One possible issue that some people may find is that you can only alter the position by one place with this method, i.e. to move image 278 to appear first I'd have to make it second, then first, otherwise the current first image would appear third.

Answer 2

while 1 is a bit ugly you can do:

  UPDATE table set order=order+1 where order>='orderValueOfItemYouCareAbout';

this will update all the rest of the images and you wont have to do a ton of leg work.