I'm working on a personal project that basically saves the output from a rich-text editor into a database so it can be accessed again later on a different device. The user can add "pages" to the notes for different chapters or subjects.
The problem I ran into is that I'm unable to rearrange the order of the notes, or insert a new page somewhere. My current solution is to have 2 IDs - one is the primary index (ID), and the other one is a relative ID (RID). To rearrange the rows, I just change the RID of that row, then sort the rows by RID when displaying them.
ID | RID | page_title | page_content
01 | 01 | Hello | Hello world
02 | 02 | Goodbye | See ya
This works when I have very few pages, but if I had 100 pages and I'd like to rearrange or insert a new row in the middle, I'd have to change the RID for every row below where the change was made.
What's the proper way to structure my table such that it's easy to rearrange or insert into later? I'm familiar with MySQL using PhpMyAdmin, and SQLite using SQLAlchemy.
Sorry if this has been asked before, it's hard to phrase the question into a google search, I kept getting irrelevant results. Thanks in advance!
There are many ways to do this. One simple way would be to treat the pages as a linked list. Instead of an order column, use a prior column that points to (FK) the page that preceded these page.
E.g. starting with pages in a-b-c-d order:
id, page name, prior
1, a, null
2, b, 1
3, c, 2
4, d, 3
5, e, 4
To move page “b” to after page “d”, you would:
(1) change c’s prior to 1
(2) change b’s prior to 4
(3) change e’s prior to 2
Do all that inside a transaction and you’re golden. The new list looks like this:
id, page name, prior
1, a, null
2, b, 4
3, c, 1
4, d, 3
5, e, 2
The same number of operations (3 for a singly-linked list) will be needed no matter how many total pages you have.
Be sure to test your implementation for moving the first page (prior=null) since that’s a special case.