Mantenimento degli indici per la posizione in un elenco ordinato nelle righe del database

Per un numero limitato di elementi (diciamo < 50.000) la soluzione più efficace e semplice è probabilmente quella di caricare tutti gli elementi in memoria, consentire all'utente di riordinarli come gli piace, rigenerare gli indici e poi aggiornare tutti ha cambiato i record in una singola transazione. Se si prevede di modificare solo una frazione di elementi in questo modo, è necessario tenere traccia dei record modificati e aggiornare solo quelli. Altrimenti, aggiornali tutti.

Se hai bisogno di una soluzione che eviti il caricamento di tutti i record in memoria e permetta di applicare "piccoli movimenti" in modo molto efficace, puoi prima assegnare i numeri di indice con "spazi" come 10, 20, 30, ... ai tuoi registri Quando devi spostare l'elemento numero 4 (con il numero 40) in seconda posizione, prova ad assegnare il valore mediano dal primo e dal nuovo terzo elemento (che significa in questo esempio 15) all'elemento numero 4. Ciò consentirà molti piccoli riordini, fino a raggiungere il punto in cui non vi è più "spazio libero" tra due numeri consecutivi. Questo è il punto in cui rinumerai tutti gli elementi nei passaggi di 10 di nuovo. Questa strategia potrebbe essere ulteriormente migliorata, ad esempio con "rinumerazioni parziali", o usando i float (grazie al commento di Dan Pichelman), e renderà la necessità di cambiare molti record un evento (si spera) di rado. Ma per sapere se questo funziona per il tuo caso, dovresti avere un'idea sul tipo di riordino che ti aspetti.

Se sposti un oggetto, solo quelli che si trovano tra la vecchia e la nuova posizione, inclusi, devono ricevere nuovi valori di sort_index. Se un oggetto si sposta dalla posizione 4 alla posizione 2, solo 2, 3 e amp; 4 sono interessati; 1 è ancora 1, 5 è ancora 5 ecc.

declare @id int;
declare @old int = 4;  -- the value in the original question
declare @new int = 2;
declare @lower int;
declare @upper int;

select @lower = MIN(c1)
from (values (@old),(@new)) as t1(c1);

select @upper = MAX(c1)
from (values (@old),(@new)) as t1(c1);

update #items
set si = si + SIGN(@old - @new)
where si between @lower and @upper;

update #items
set si = @new
where id = @id;

L'ho provato contro SQL Server 2008R2. La sintassi del tuo RDBMS può variare. Il secondo UPDATE potrebbe essere eliminato con un'impostazione più intelligente di @upper o @lower , a seconda della direzione dello spostamento. Dato che è probabile che qualsiasi sistema reale abbia un vincolo univoco su sort_index e quindi un indice, è probabile che l'aggiornamento sia abbastanza efficiente. Il numero di righe modificate dipenderà da quanto è distante la nuova posizione dell'articolo dalla precedente. Suggerirei che in qualsiasi interfaccia utente praticabile questo sarebbe piuttosto modesto. Conosci i tuoi dati al meglio, comunque.

Per inciso, il design del tuo tavolo non è normalizzato. Se un oggetto fosse in 50 progetti, dovrebbe avere 50 righe nella tabella degli articoli, ognuno con diversi valori project_sort_index ma tutti con lo stesso sort_index. Un design migliore sarebbe

create table project (
    id int auto increment primary key, 
    name text);

create table item (
    id int auto increment primary key, 
    name text,
    sort_index int not null);

create table project_item (
    item_id int not null,
    project_id int not null, 
    project_sort_index int not null);

Un'altra soluzione sarebbe quella di conservare i tuoi articoli come una lista doppiamente collegata

create table item (
    id int auto increment primary key, 
    name text,
    preceeding_item_id int,    -- have to be NULLable for first & last items.
    succeeding_item_id int);

Una mossa quindi aggiornerebbe al massimo 5 righe: i vicini dalla vecchia posizione, quelli nella nuova e l'oggetto stesso. L'applicazione recupera tutte le righe e gestisce la sequenza su schermo.