Timeline for How to design a database for storing a sorted list?
Current License: CC BY-SA 3.0
10 events
when toggle format | what | by | license | comment | |
---|---|---|---|---|---|
Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
Sep 14, 2011 at 15:20 | comment | added | Nick Chammas | @chitti - The accepted answer to that SO question is great. It suggests the same approach I've detailed here, with the additional suggestion of using decimals instead of integers to greatly expand your flexibility in assigning and changing ranks. Great find. | |
Sep 14, 2011 at 7:45 | comment | added | chitti | Thanks for the updated answer. The 'rank' is an arbitrary property of my data. I am almost convinced that a custom index column is what I require. Check out this SO link with a similar question. The top answer provides recommendations on how to handle such a rank column. | |
Sep 13, 2011 at 13:49 | history | edited | Nick Chammas | CC BY-SA 3.0 |
expanded to include detail based on asker's expected load profile
|
Sep 13, 2011 at 1:28 | comment | added | Nick Chammas |
@chitti - This is kinda funny, actually. This is exactly the problem database engines deal with when indexing data, because they are ordering it and re-ordering it as data is added or changed. If you look up FILLFACTOR you will see it is basically meant to create that extra space for records in an index, just as the rank gaps I described create space for rank changes and insertions.
|
|
Sep 13, 2011 at 1:21 | comment | added | Nick Chammas | @chitti - Ah, that is a concern. You could space out your rankings (e.g. 0, 1000, 2000, 3000, ...) and periodically re-rank all records as the rank gaps fill up. This will not scale if you expect much more than a few tens of thousands of records, though. | |
Sep 13, 2011 at 1:18 | history | edited | Nick Chammas | CC BY-SA 3.0 |
added specific responses to query needs
|
Sep 13, 2011 at 1:16 | comment | added | chitti | Ranks are modifiable. I am expecting the ranks to change constantly and new records being inserted constantly. I am worried about the case when I insert a new element with a rank then the ranks of all records below the new record in sort order need to changed. Isn't that a costly operation when I have thousands of records in my database? | |
Sep 13, 2011 at 1:11 | history | edited | Nick Chammas | CC BY-SA 3.0 |
added 28 characters in body
|
Sep 13, 2011 at 1:03 | history | answered | Nick Chammas | CC BY-SA 3.0 |