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.
我不确定最好的方法是什么，因为我从来没有在数据库中实现排序，而且我对并发并不熟悉。正因为如此，我读过关于锁定MySQL表的文章，我不确定是否应该实现它。
Here are my two ideas:
以下是我的两个想法:
-
Add a column named
order_numto theimagestable. Lock the table and allow the client to rearrange the order of the images, then update the table and unlock it.在图像表中添加名为order_num的列。锁定表并允许客户端重新排列图像的顺序，然后更新表并解锁它。
-
Add a column named
order_numto theimagestable (just as idea 1 above). Allow the client to update one image's place at a time without locking.向images表中添加一个名为order_num的列(与上面的想法1一样)。允许客户端在没有锁定的情况下更新一个图像的位置。
Thanks!
谢谢!
3 个解决方案
#1
4
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.
在一个基于web的世界中，您不希望锁定一个用户进行编辑的表，然后等待他们完成对表的解锁。用户1在这个场景中可能永远不会回来,他们可能会失去他们的会话,或者浏览器崩溃,等等。这意味着你要做大量的工作来找出什么时候解锁,加上代码,让用户2知道表的锁,他们不会做任何事情。
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.
我建议采用这种设计:让它们都进入编辑模式(可能是在浏览器中)，使用一些javascript。他们可以拖拽图片，直到他们高兴，然后他们提交全部的订单。在一个事务中更新数据库的order_num字段。
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.
在这个场景中，最糟糕的事情是用户1和用户2同时进行编辑，而最后编辑的人是顺序保持不变的人。也许它们同时更新，但是数据库会处理它，因为它会对事务进行排队。
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.
我不想回避你的问题，但这是我的想法。
#2
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.
如果不希望“每个用户sortin”，order_num列似乎是正确的方式。如果为存储子系统选择InnoDB，则可以使用事务，而不必锁表。
#3
0
Relational database and hierarchy: I use id (auto increment) and parent columns to achieve hierarchy. A parent of zero is always the root element. You could order by id, parent.
关系数据库和层次结构:我使用id(自动增量)和父列来实现层次结构。0的父元素总是根元素。您可以通过id, parent来订购。
Concurrency: This is an easy way to deal with concurrency. Use a version column. If the version has changed since user 1 started editing, block the save, offer to reload edit. Increment the version after each successful edit.
并发性:这是一种处理并发性的简单方法。使用一个版本列。如果自用户1开始编辑以来版本发生了更改，请阻止保存，并提供重新加载编辑。在每次成功编辑后增加版本。
#1
4
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.
在一个基于web的世界中，您不希望锁定一个用户进行编辑的表，然后等待他们完成对表的解锁。用户1在这个场景中可能永远不会回来,他们可能会失去他们的会话,或者浏览器崩溃,等等。这意味着你要做大量的工作来找出什么时候解锁,加上代码,让用户2知道表的锁,他们不会做任何事情。
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.
我建议采用这种设计:让它们都进入编辑模式(可能是在浏览器中)，使用一些javascript。他们可以拖拽图片，直到他们高兴，然后他们提交全部的订单。在一个事务中更新数据库的order_num字段。
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.
在这个场景中，最糟糕的事情是用户1和用户2同时进行编辑，而最后编辑的人是顺序保持不变的人。也许它们同时更新，但是数据库会处理它，因为它会对事务进行排队。
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.
我不想回避你的问题，但这是我的想法。
#2
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.
如果不希望“每个用户sortin”，order_num列似乎是正确的方式。如果为存储子系统选择InnoDB，则可以使用事务，而不必锁表。
#3
0
Relational database and hierarchy: I use id (auto increment) and parent columns to achieve hierarchy. A parent of zero is always the root element. You could order by id, parent.
关系数据库和层次结构:我使用id(自动增量)和父列来实现层次结构。0的父元素总是根元素。您可以通过id, parent来订购。
Concurrency: This is an easy way to deal with concurrency. Use a version column. If the version has changed since user 1 started editing, block the save, offer to reload edit. Increment the version after each successful edit.
并发性:这是一种处理并发性的简单方法。使用一个版本列。如果自用户1开始编辑以来版本发生了更改，请阻止保存，并提供重新加载编辑。在每次成功编辑后增加版本。