I have a fairly simple data structure like this:
create table project (id int auto increment primary key, name text); create table item (id int auto increment primary key, name text, project_id int not null, project_sort_index int not null, sort_index int not null, foreign key fk_project(project_id) references project(id));
project can have many
items. Items have two different fields for maintaining sort order,
These sort order fields apply in the following way. When I view all items belonging to a project, I need a sort order specific to each project for the items. When I view all items globally, I need a sort order for them globally.
I have a couple of questions as to how best to maintain and modify sort order for these items. Lets say that I have moved an item at index 4 to index 2. How do I propagate that change to my database efficiently?
For example, how do I now update all index numbers >= 2 to move everything down, yet close the gap left in place 4? Is there a better way of sorting lists in SQL?