Whenever I have some records/objects that I want to be in a certain order, I usually create a field called Ordinal.
每当我有一些我希望按特定顺序排列的记录/对象时,我通常会创建一个名为Ordinal的字段。
I often wonder if it would be better to use an integer or a decimal value for the ordinal field.
我常常想知道对序数字段使用整数或小数值是否更好。
This is a consideration when moving an object to a different position in the order:
在将对象移动到顺序中的不同位置时,这是一个考虑因素:
-
If you use consecutive integers, you have to do some serious reworking of all of the ordinals (or at least the ordinals that fall before the original position of the object being moved).
如果使用连续的整数,则必须对所有序数进行一些认真的重写(或者至少是在移动对象的原始位置之前的序数)。
-
If you use integers but space them out (maybe at 1000 intervals), then you can just change the ordinal to a mid point value between the surrounding objects where you want to move the object. This could fail if somewhere down the line you end up with consecutive integers.
如果使用整数但是将它们分开(可能是1000个间隔),那么您只需将序数更改为要移动对象的周围对象之间的中点值。如果在某个地方你最终会得到连续的整数,这可能会失败。
-
If you use decimal numbers you could just find the average of the surround object's ordinals and use that for the object to be moved.
如果使用十进制数字,您可以找到环绕对象的序数的平均值,并将其用于要移动的对象。
-
Maybe it would be possible to use a string, but I could see that getting pretty goofy.
也许可以使用一个字符串,但我可以看到变得非常愚蠢。
I'm sure there are other considerations I haven't thought of. What do you use and why?
我确信还有其他一些我没有想过的考虑因素。你用了什么?为什么?
6 个解决方案
#1
"This could fail if somewhere down the line you end up with consecutive integers."
“如果在某个地方你最终会得到连续的整数,那么这可能会失败。”
For this (probably rare and thus not performance important) case, you could implement a renumber method that spaces out again. When I used to program in COMAL (anyone know that language?), you could do this very thing with line numbers.
对于这种情况(可能很少见,因此性能不重要),您可以实现重新分区的重新编号方法。当我以前在COMAL中编程时(任何人都知道那种语言?),你可以用行号做这件事。
#2
Decimals seem to solve your problem pretty well. Since Decimals are just base 10 floats, you actually have a lot of digits available. Unless you've seen cases where you've gotten out to quite a few digits and had reason to suspect a reason for an unlimited number of digits being necessary, I'd let it ride.
小数似乎很好地解决了你的问题。由于小数只是基数为10的浮点数,因此实际上有很多数字可用。除非你已经看到你已经得到了很多数字且有理由怀疑有必要使用无限数字的原因的情况,否则我会让它骑行。
If you really need an alternative and don't see a need to stick with a basic data bype, you might go with tumbler arithmetic. The basic idea is that it's a place notation that is infinitely expandable at each position. Pretty simple conceptually.
如果你真的需要一个替代品,并且不需要坚持使用基本数据bype,那么你可能会选择不倒翁算法。基本思想是它是一个可以在每个位置无限扩展的地方符号。概念上非常简单。
#3
I used to use a decimal type for a field of this kind to order records in a table, which we actually exposed to the customer so that they could set their own order. Although it sounds cheesy our customers liked it; they found it very intuitive. They caught on very quickly that they could use numbers like 21.5 to move something between 21 and 22.
我曾经使用十进制类型作为这种类型的字段来订购表中的记录,我们实际上将这些记录暴露给客户,以便他们可以设置自己的订单。虽然听起来很俗气但我们的客户喜欢它;他们发现它非常直观。他们非常迅速地发现他们可以使用像21.5这样的数字来移动21到22之间的东西。
Maybe it's because they were accountants.
也许是因为他们是会计师。
#4
I use integers and just rearrange as necessary when a new item needs to be inserted in the middle of the order. Since you can create the necessary gap with a single update statement, it's fairly trivial. However, I've only ever done this on lookup tables of a few dozen rows at most, obviously this scales a bit poorly. But I would say that if you need a solution to this problem for a large number of rows, the process(es) for maintaining the order should be proceduralized anyway, which makes the choice of data type largely moot.
我使用整数,只需在需要在订单中间插入新项目时重新排列。由于您可以使用单个更新语句创建必要的间隙,因此它非常简单。但是,我最多只在最多几十行的查找表上完成此操作,显然这有点差。但我要说的是,如果你需要为大量行解决这个问题,那么维护订单的过程无论如何都应该是程序化的,这使得数据类型的选择在很大程度上没有实际意义。
#5
I remember this being a similar question to a previous post. It can be found here:
我记得这是与前一篇文章类似的问题。在这里能找到它:
SQL Server优先级排序
The linked list would still work, but this is a much easier solution if you don't want to track a parent child relationship.
链接列表仍然有效,但如果您不想跟踪父子关系,这是一个更容易的解决方案。
Sounds like what you want is a linked list. That way you always know what comes next and you don't have to guess. So the position field would be a pointer to the object following it.
听起来你想要的是一个链表。这样你总能知道接下来会发生什么,你不必猜测。所以position字段将是指向它后面的对象的指针。
The problem I have always had with using arbitrary numbers for position, is that it can quickly fall to entropy. What if more items get added and the number become consecutive etc. etc. It can quickly become unmanageable if the list of items changes position.
我一直使用任意数字作为位置的问题是它可以迅速降至熵。如果添加更多项目并且数量变为连续等等怎么办。如果项目列表改变位置,则很快就会变得难以管理。
To implement this in sql server table, add another field with the same data type as the primary key. If the field is null then it is the bottom element in the list. If you are storing multiple lists in the same table you will probably want to add another field called ListID which designates all rows with the same ListID pertain to the same list. So something like this.
要在sql server表中实现此功能,请添加与主键具有相同数据类型的另一个字段。如果该字段为null,则它是列表中的底部元素。如果要在同一个表中存储多个列表,则可能需要添加另一个名为ListID的字段,该字段指定具有相同ListID的所有行属于同一列表。所以这样的事情。
Table:
ID INT
ListID INT
Child INT
Pararent Row For first list:
1, 1, 2
First Child
2, 1, 3
Second Child
3, 1, NULL
Parent Row for second list:
4, 2, 5
First Child
5, 2, 6
Second Child
6, 2, NULL
You'll probably have to do an insert and an update every time you add a row, which can be a little tedious, but it will always make the list line up.
每次添加行时,您可能不得不进行插入和更新,这可能有点单调乏味,但它总是会使列表排成一行。
#6
Is the "certain order" based on data outside of the table? If so, why not include it so you can do the sorting dynamically? If it's already in the table, adding a field is redundant.
“某些订单”是否基于表外的数据?如果是这样,为什么不包括它,以便您可以动态进行排序?如果它已经在表中,则添加字段是多余的。
#1
"This could fail if somewhere down the line you end up with consecutive integers."
“如果在某个地方你最终会得到连续的整数,那么这可能会失败。”
For this (probably rare and thus not performance important) case, you could implement a renumber method that spaces out again. When I used to program in COMAL (anyone know that language?), you could do this very thing with line numbers.
对于这种情况(可能很少见,因此性能不重要),您可以实现重新分区的重新编号方法。当我以前在COMAL中编程时(任何人都知道那种语言?),你可以用行号做这件事。
#2
Decimals seem to solve your problem pretty well. Since Decimals are just base 10 floats, you actually have a lot of digits available. Unless you've seen cases where you've gotten out to quite a few digits and had reason to suspect a reason for an unlimited number of digits being necessary, I'd let it ride.
小数似乎很好地解决了你的问题。由于小数只是基数为10的浮点数,因此实际上有很多数字可用。除非你已经看到你已经得到了很多数字且有理由怀疑有必要使用无限数字的原因的情况,否则我会让它骑行。
If you really need an alternative and don't see a need to stick with a basic data bype, you might go with tumbler arithmetic. The basic idea is that it's a place notation that is infinitely expandable at each position. Pretty simple conceptually.
如果你真的需要一个替代品,并且不需要坚持使用基本数据bype,那么你可能会选择不倒翁算法。基本思想是它是一个可以在每个位置无限扩展的地方符号。概念上非常简单。
#3
I used to use a decimal type for a field of this kind to order records in a table, which we actually exposed to the customer so that they could set their own order. Although it sounds cheesy our customers liked it; they found it very intuitive. They caught on very quickly that they could use numbers like 21.5 to move something between 21 and 22.
我曾经使用十进制类型作为这种类型的字段来订购表中的记录,我们实际上将这些记录暴露给客户,以便他们可以设置自己的订单。虽然听起来很俗气但我们的客户喜欢它;他们发现它非常直观。他们非常迅速地发现他们可以使用像21.5这样的数字来移动21到22之间的东西。
Maybe it's because they were accountants.
也许是因为他们是会计师。
#4
I use integers and just rearrange as necessary when a new item needs to be inserted in the middle of the order. Since you can create the necessary gap with a single update statement, it's fairly trivial. However, I've only ever done this on lookup tables of a few dozen rows at most, obviously this scales a bit poorly. But I would say that if you need a solution to this problem for a large number of rows, the process(es) for maintaining the order should be proceduralized anyway, which makes the choice of data type largely moot.
我使用整数,只需在需要在订单中间插入新项目时重新排列。由于您可以使用单个更新语句创建必要的间隙,因此它非常简单。但是,我最多只在最多几十行的查找表上完成此操作,显然这有点差。但我要说的是,如果你需要为大量行解决这个问题,那么维护订单的过程无论如何都应该是程序化的,这使得数据类型的选择在很大程度上没有实际意义。
#5
I remember this being a similar question to a previous post. It can be found here:
我记得这是与前一篇文章类似的问题。在这里能找到它:
SQL Server优先级排序
The linked list would still work, but this is a much easier solution if you don't want to track a parent child relationship.
链接列表仍然有效,但如果您不想跟踪父子关系,这是一个更容易的解决方案。
Sounds like what you want is a linked list. That way you always know what comes next and you don't have to guess. So the position field would be a pointer to the object following it.
听起来你想要的是一个链表。这样你总能知道接下来会发生什么,你不必猜测。所以position字段将是指向它后面的对象的指针。
The problem I have always had with using arbitrary numbers for position, is that it can quickly fall to entropy. What if more items get added and the number become consecutive etc. etc. It can quickly become unmanageable if the list of items changes position.
我一直使用任意数字作为位置的问题是它可以迅速降至熵。如果添加更多项目并且数量变为连续等等怎么办。如果项目列表改变位置,则很快就会变得难以管理。
To implement this in sql server table, add another field with the same data type as the primary key. If the field is null then it is the bottom element in the list. If you are storing multiple lists in the same table you will probably want to add another field called ListID which designates all rows with the same ListID pertain to the same list. So something like this.
要在sql server表中实现此功能,请添加与主键具有相同数据类型的另一个字段。如果该字段为null,则它是列表中的底部元素。如果要在同一个表中存储多个列表,则可能需要添加另一个名为ListID的字段,该字段指定具有相同ListID的所有行属于同一列表。所以这样的事情。
Table:
ID INT
ListID INT
Child INT
Pararent Row For first list:
1, 1, 2
First Child
2, 1, 3
Second Child
3, 1, NULL
Parent Row for second list:
4, 2, 5
First Child
5, 2, 6
Second Child
6, 2, NULL
You'll probably have to do an insert and an update every time you add a row, which can be a little tedious, but it will always make the list line up.
每次添加行时,您可能不得不进行插入和更新,这可能有点单调乏味,但它总是会使列表排成一行。
#6
Is the "certain order" based on data outside of the table? If so, why not include it so you can do the sorting dynamically? If it's already in the table, adding a field is redundant.
“某些订单”是否基于表外的数据?如果是这样,为什么不包括它,以便您可以动态进行排序?如果它已经在表中,则添加字段是多余的。