I have something like a link list, where a user itself can define, in which order those links are displayed ( depended on their needs, preferences )... so they
can for example drag the last link and drop it somewhere between the rest of the links..

this rank order has to be stored somehow in my DB, hence the same order can be displayed when the user stops by the next time..

First I thought about a INT field 'rank', where the actual rank number (i.e. 1, 2, 3 etc.) is stored.. but that turned out to be crappy.. because almost everytime the user drag & drops a link, I have to change a lot of records to keep the data integrity..

Then I had another idea.. change that INT field 'rank' to a FLOAT field. when a user now drags a link and drops it between two other links, I only fetch the rank number of those two links, take the average (sum up, divide 2) and this is my new page rank..

but I'm not overhappy with this solution... I think, there must be something else..


anyone can help??


thanks in advance

that is actually a very good solution

it was the best I could come up with..

but isn't there a sort of pattern to solve this issue? I'm sure, I'm not the first one doing this (:

yes, i have seen people try to solve this in many different ways

all of which involve re-sequencing existing rows after an insert

if using consecutive sequence numbers, this involves updating maybe half the rows in the entire table!!

at one point i used to recommend people assign their sequence numbers in increments of 100, so that you can, for example, easily fit 350 in between 300 and 400

but this can also lead to the need to re-sequence

floating point numbers have the advantage that you should never need to re-sequence, as there will always be a valid number in between any two given numbers

... in theory! In practice computers are finite, and there must come a point where you cannot go further. But this still sounds like the best solution!

ah yeah.. now I'm really proud of myself.. (:

@andrewst: that's true, but I think for my app it will suffice.. do you know how many decimal places a float can have?

That really depends on the database implementation...

Many databases use implementations of IEEE Standard 754 for Binary Floating-Point numbers. IEEE floating points are single precision (up to 6 digits of precision) or double precision (15 digits precision).

However, database vendors will often include aribritary precision NUMERIC types as well, which support floating numbers of aribritaty precision (up to some limit...)

For example, both Oracle and SQL Server support NUMERIC data types with up to 38 digits of precision, whereas PostgreSQL supports NUMERIC data types with up to 1000 digits of precision by default. (If you want to recompile PostgreSQL, you can define the upper limit to any length you desire...) However, arithmetic on numeric values is slow when compared to the integer types, or to the IEEE floating-point types. (In SQL Server, Float and Real correspond to IEEE Double and Single Precision, respectively.)

I kind of like the idea of the floats, but storing integers and changing the surrounding rank values isn't too slow if you have a small number of items to change. You might just want to try this first, and profile it to see how slow it actually is.