Which data type to use for ordinal?

Question

Whenever I have some records/objects that I want to be in a certain order, I usually create a field called 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.

• 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?

Answer 1

I remember this being a similar question to a previous post. It can be found here:

http://stackoverflow.com/questions/112551/sql-server-priority-ordering/112565#112565

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.

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.

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.

Answer 2

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.

Maybe it's because they were accountants.

Answer 3

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.

Answer 4

"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.

Answer 5

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.

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.

Answer 6

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.