如何设计可以重新排序的表？ [英] How to design table that can be re-sequenced?

问题描述

我需要做一个关于数据库的设计决策。要求是一个数据库表具有 AUTO_INCREMENT PRIMARY KEY 字段，名为 id 。默认情况下，系统会向用户显示（按网络）按 ID 排序的每一行。例如，如果表中有4条记录。用户界面将按 0，1，2，3 的顺序显示行。

I need to make a design decision about database. The requirment is that one database table has an AUTO_INCREMENT PRIMARY KEY field called id. By default, each row is shown to user (in web) sorted ascendenting by id. For example, if there are 4 records in the table. The UI will show rows in sequence of 0, 1, 2, 3.

该用户可以拖动&在UI中删除行以更改序列。说，用户拖动rom 3并将其下降到0.因此，显示顺序变为 3，0，1，2 。这个序列应该持久化到数据库中。

Now, there is requirement that user can drag & drop row in UI to change sequence. Say, user drag rom 3 and drop it befow 0. So, the display sequence turns to be 3, 0, 1, 2. This sequence should be persistent into database.

我想知道如何设计数据库表，使这个持久性和可扩展性。我的第一个想法是每行都有一个 序列 字段，表示显示序列。默认情况下，该值应与 ID 相同。从数据库中选择要显示的数据时，行按顺序而不是 ID 升序排列。

I'm wondering how to design database table to make this persistent and scalable. My first thought is that each row has a "sequence" field indicating the display sequence. By default, the value should be same as id. When selecting data from database for display, the rows are sorted ascending on sequence instead of id.

被更改，然后更新为新值。结果是，它可能涉及在其他行中的很多更改。以上面的例子，原来表格是这样的：

If sequence is changed, then it is updated to new value. The result is that it may involves a lot of changes in other rows. Taking example above, originally the table is like this:

|id   | sequence |
|0    | 0        |
|1    | 1        |
|2    | 2        |
|3    | 3        |

现在，将id为3的行拖到第一行。其序列更新为0.同时，ID为0,1,2的行也应更新。

Now, after drag row with id 3 to first. Its sequence is updated to 0. At the same time, row with id 0, 1, 2 should also be updated.

|id   | sequence |
|0    | 1        |
|1    | 2        |
|2    | 3        |
|3    | 0        |

恐怕这种方法会使重新排序成本大量资源，因此，我假设序列可以通过将 id 乘以K（例如10）来初始化。这会在插入序列之间留下空白。但是，如果将K + 1行移动到此间隙，则间隙仍可能消耗。

I'm afraid this approach will make re-sequence cost a lot resource and not scalable. So, I suppose the sequence can be intiialized by multiplying id with K(say, 10). This leaves gaps between sequence value for insertion. However, the gap can still consumed up if K+1 rows are moved to this gap.

|id   | sequence |
|0    | 0        |
|1    | 10       |
|2    | 20       |
|3    | 30       |

这似乎是数据库设计的一个常见问题。

This seems a common problem to database design. Anybody has better idea to achive this?

推荐答案

对我来说明显的答案是使用你提到的最后一个解决方案，浮动）。

The obvious answer to me is to use the last solution you mentioned but with decimals (floats).

因此，您首先要说： {0.1,0.2,0.3,0.4,0.5} 。如果将最后一个项目移动到 0.2 和 0.3 之间，它将变为 0.25 。如果将它移动到顶部，它将变为 0.05 。每次你简单地取两个数字的中间点。换句话说，是上一个/下一个项目的平均值。

So you start with, say: {0.1, 0.2, 0.3, 0.4, 0.5}. If you move the last item to between 0.2 and 0.3 it becomes 0.25. If you move it to the top it becomes 0.05. Each time you simply take the mid-point of the two numbers on either side. In other words, the average of the previous/next items.

另一个类似的解决方案是使用字符，然后按字母顺序排序。从 {1，2，3，4，5} 开始，如果你移动5在2和3之间，你会使用25.如果你做一个字符串类型的列出您保留的正确顺序： {1,2,25,3,4} 。

Another similar solution is to use characters, then sort by strings alphabetically. Starting with {1, 2, 3, 4, 5}, if you move the 5 between 2 and 3 you'd use 25. If you do a string sort of the list you keep the right order: {1, 2, 25, 3, 4}.

唯一的问题我可以想到的这些方法是，最终，你会达到浮点精度的极限，即试图找到 0.0078125 和 0.0078124之间的数字。有几种方法可以解决这个问题：

The only problem I can think of with these methods is that eventually, you will hit the limit of floating point precision, i.e. trying to find a number between 0.0078125 and 0.0078124. A few ways to solve this:

• 每隔一段时间运行脚本，遍历每个项目，并将它们重新排序为 {0.1，0.2，0.3，...} 。


• 不能使用两个小数位。在 0.2 和 0.25 之间可以使用 0.23 0.225 。


• 在本地重新排序，而不是全局重新排序。如果您有 {0.2,0.3,0.6} ，并希望在 0.2 后插入，则可以将第二个 0.4 ，并将新项目插入 0.3 。

• Run a script every so often that runs through every item and reorders them to {0.1, 0.2, 0.3, ...}.
• Don't use two decimal places when you can use one. Between 0.2 and 0.25 you could use 0.23 instead of the calculated 0.225.
• Re-sequence locally, not globally. If you have {0.2, 0.3, 0.6} and want to insert after 0.2, you could set the second one to 0.4 and insert the new item at 0.3.

这篇关于如何设计可以重新排序的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！