Implementing order in a PHP/MySQL CMS & dealing with concurrency.

Question

I have the following tables:

=======================           =======================
| galleries           |           | images              |
|---------------------|           |---------------------|
| PK | gallery_id     |<--\       | PK | image_id       |
|    | name           |    \      |    | title          |
|    | description    |     \     |    | description    |
|    | max_images     |      \    |    | filename       |
=======================       \-->| FK | gallery_id     |
                                  =======================

I need to implement a way for the images that are associated with a gallery to be sorted into a specific order. It is my understanding that relational databases are not designed for hierarchical ordering.

I also wish to prepare for the possibility of concurrency, even though it is highly unlikely to be an issue in my current project, as it is a single-user app. (So, the priority here is dealing with allowing the user to rearrange the order).

I am not sure the best way to go about this, as I have never implemented ordering in a database and am new to concurrency. Because of this I have read about locking MySQL tables and am not sure if this is a situation where I should implement it.

Here are my two ideas:

1. Add a column named order_num to the images table. Lock the table and allow the client to rearrange the order of the images, then update the table and unlock it.

2. Add a column named order_num to the images table (just as idea 1 above). Allow the client to update one image's place at a time without locking.

Thanks!

Answer 1

If you don't want "per user sortin" the order_num column seems the right way to go. If you choose InnoDB for your storage subsystem you can use transactions and won't have to lock the table.

Answer 2

Here's my thought: you don't want to put too many man-hours into a problem that isn't likely to happen. Therefore, take a simple solution that's not going to cause a lot of side effects, and fix it later if it's a problem.

In a web-based world, you don't want to lock a table for a user to do edits and then wait until they're done to unlock the table. User 1 in this scenario may never come back, they may lose their session, or their browser could crash, etc. That means you have to do a lot of work to figure out when to unlock the table, plus code to let user 2 know that the table's locked, and they can't do anything with it.

I'd suggest this design instead: let them both go into edit mode, probably in their browser, with some javascript. They can drag images around in order until their happy, then they submit the order in full. You update your order_num field in a single transaction to the database.

In this scenario the worst thing that happens is that user 1 and user 2 are editing at the same time, and whoever edits last is the one whose order is preserved. Maybe they update at the exact same time, but the database will handle that, as it's going to queue up transactions.

The fallback to this problem is that whoever got their order overwritten has to do it again. Annoying but there's no loss, and the code to implement this is much simpler than the code to handle locking.

I hate to sidestep your question, but that's my thoughts about it.