Timeline for Storing a re-orderable list in a database
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 20, 2020 at 12:25 | comment | added | James Anderson | Another possibility is to use an asci string -- start with Blue - "A", Red "B" and Green "C" to add purple between Blue and Red give it the code "AJ" ro add pink between Purple and Red give it the code "AP", eventually you could end up with a code like "AKELA" after thousands of inserts. | |
| Jul 12, 2020 at 16:09 | comment | added | Gal Bracha | @user40980 If I understand correctly - For mongo - if you index your column then that "inefficiency" is not peneltied upon reading only upon writing the data. | |
| Jan 28, 2017 at 15:51 | comment | added | zelk | Furthermore, if you ever hit a case where you run out of space between numbers... it's not that hard to fix. Move one of them. But the important thing is that this works in a best-effort kind of way, which handles many concurrent edits by different parties (e.g. trello). You can split two numbers, maybe even sprinkle a bit of random noise, and voila, even if someone else did the same thing at the same time there is still a global order, and you didn't need to INSERT inside a transaction to get there. | |
| Jan 28, 2017 at 15:42 | comment | added | zelk | @PieterB As for 'why not use a 64 bit integer', it's mostly ergonomics for the developer, I would say. There is approximately as much bit depth <1.0 as there is >1.0 for your average float, so you can default the 'position' column to 1.0 and insert 0.5, 0.25, 0.75 just as easily as doubling. With integers, your default would have to be 2^30 or so, makes it a bit tricky to think about when you're debugging. Is 4073741824 bigger than 496359787? Start counting digits. | |
| Jan 28, 2017 at 15:34 | comment | added | zelk |
For the naysayers, this solution is exactly what Trello (trello.com) does. Open up your chrome debugger and diff the json output from before/after a reorder (drag/drop a card) and you get - "pos": 1310719, + "pos": 638975.5. To be fair, most people don't do trello lists with 4 million entries in them, but Trello's list size & use case is pretty common for user-sortable content. And anything user-sortable has approximately nothing to do with high performance, int vs float sorting speed is moot for that, especially considering databases are mostly constrained by IO performance.
|
|
| Nov 28, 2016 at 13:31 | comment | added | Pieter B | Also the difference between using an integer and a floating point number is non-existant. It's about the "bittness". A 32 bit float will run into issues just as fast as a 32 bit integer. You would need like a 64 bit float, but then, why not use a 64 bit integer? | |
| Nov 28, 2016 at 13:18 | comment | added | Pieter B | Using gaps is a blueprint for disaster. Your gaps act like a binary search and will at any larger scale prove to be insufficient. | |
| Apr 18, 2013 at 9:02 | history | edited | James Anderson | CC BY-SA 3.0 |
added 587 characters in body
|
| Apr 18, 2013 at 5:58 | comment | added | James Anderson | But any scheme using ints means you need to update all/most of the rows in the list every time the order changes. Using floats you only update the row which moved. Also "floats more expensive than ints" very much depends on the implementation and hardware used. Certainly the extra cpu involved is insignificant compared with the cpu required to update a row and its associated indexes. | |
| Apr 18, 2013 at 2:06 | comment | added | user40980 | Indexing and sorting in a datbase based on floats is more expensive than ints. Ints also are a nice ordinal type... doesn't need to be sent as bits to be able to be sorted on the client (the difference between two numbers that render the same when printed, but have different bit values). | |
| Apr 18, 2013 at 1:48 | history | answered | James Anderson | CC BY-SA 3.0 |