在数据库中存储项目顺序有哪些方法？

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

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.