Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Question store userdefined rank order

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is actually a very good solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    Quote Originally Posted by r937
    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 (:

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    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!

  6. #6
    Join Date
    Oct 2007
    Posts
    3
    Quote Originally Posted by r937
    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
    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?

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by tas
    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.)
    Last edited by loquin; 10-02-07 at 12:25.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Aug 2007
    Posts
    10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •