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));A project can have many items. Items have two different fields for maintaining sort order, project_sort_index and sort_index.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?
Maintaining indices for location in a sorted list in database rows
database design;sql