Whenever I've some records/objects that I wish to maintain a particular order, It's my job to produce a area known as Ordinal.

I frequently question if it might be easier to make use of an integer or perhaps a decimal value for that ordinal area.

This can be a consideration when moving an item to another position within the order:

  • If you are using consecutive integers, you need to do some serious reworking of all the ordinals (or at best the ordinals that fall prior to the original position from the object being moved).

  • If you are using integers but space them out (maybe at 1000 times), you'll be able to just alter the ordinal to some mid point value between your surrounding objects where you need to slowly move the object. This might fail if somewhere down the road you finish track of consecutive integers.

  • If you are using decimal amounts you can just discover the average from the surround object's ordinals and employ that for that resist be moved.

  • Maybe it might be possible to utilize a string, but I saw that getting pretty wacky.

I am sure you will find other factors I've not considered. Exactly what do you utilize and why?

"This might fail if somewhere down the road you finish track of consecutive integers."

With this (most likely rare and therefore not performance important) situation, you can implement a renumber method that spaces out again. When I did previously enter in COMAL (anybody realize that language?), you could do this this very factor with line amounts.

Decimals appear to resolve your condition pretty much. Since Decimals are simply base 10 floats, you really have lots of numbers available. Unless of course you have often seen cases when you've become to a number of numbers coupled with reason to suspect grounds to have an limitless quantity of numbers being necessary, I'd allow it to ride.

If you actually need an alternate and do not visit a have to stick to a fundamental data bype, you may opt for tumbler arithmetic. The fundamental idea is it's a place notation that's infinitely expanding each and every position. Really quite simple conceptually.

I recall this as being a similar question to some previous publish. It may be found here:

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

The linked list would still work, but this can be a much simpler solution if you won't want to track a parent or gaurdian child relationship.

Seems like what you would like is really a linked list. This way you usually understand what comes next and it's not necessary to guess. Therefore the position area will be a pointer towards the object following it.

The issue I've always had with using arbitrary amounts for position, is it can rapidly fall to entropy. Let's say more products get added and also the number become consecutive etc. etc. It may rapidly become uncontrollable when the listing of products changes position.

To implement this in sql server table, add another area with similar data type because the primary key. When the area is null then its the underside aspect in their email list. If you're storing multiple lists within the same table you'll most likely wish to add another area known as ListID which designates all rows with similar ListID have to do with exactly the same list. So something similar to 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 most likely need to do an place as well as an update any time you give a row, which could be a little tiresome, but it'll always result in the list fall into line.

I did previously make use of a decimal type for any area of the kind to buy records inside a table, which we really uncovered towards the customer to ensure that they might set their very own order. Even though it sounds cheesy our clients loved it they thought it was very intuitive. They caught on very rapidly they can use amounts like 21.5 to maneuver something between 21 and 22.

It can be simply because they were an accounting firm.