Come rendere la capacità di ordinare i record in DB?

Esistono diversi approcci che è possibile utilizzare:

1. La più semplice è di rinunciare al vincolo UNIQUE sulla colonna che specifica l'ordine (come lista collegata o come numero di sequenza). Spetta quindi al software assicurare che alla fine il database sia di nuovo coerente.

2. Utilizza i numeri di sequenza con spazi vuoti.
   Se inizialmente inizi con numeri di sequenza come 10, 20, 30, ecc., Puoi riordinarli assegnando i numeri di sequenza tra quelli già distribuiti.

   Ad esempio, per mettere il quarto elemento tra il primo e il secondo, i numeri di sequenza diventerebbero 10, 15, 20, 30, 50, ecc.

   Per evitare di esaurire gli spazi vuoti per posizionare gli elementi, puoi periodicamente normalizzare i numeri di sequenza, oppure puoi usare i numeri di sequenza in virgola mobile.

Quattro opzioni per te. Mi piace l'opzione 4 ma la prima è più semplice.

Opzione 1. Avvia solo uno più alto

I valori nella colonna Order non sono importanti, ma solo i relativi valori relativi. Sia che tu contenga dieci righe da 1 a 10, da 11 a 20 o da 57 a 66 sono tutti uguali.

Quindi, una soluzione molto semplice è solo iniziare una più alta della tua colonna Order più alta: se le tue righe hanno valori di Order da 1 a 10, e devi invertirle, assegna loro valori da 20 a 11 .

L'ordinamento terminerà correttamente e non avrai violazioni di unicità perché si trovano in un intervallo di valori separato.

Il lato negativo di questo è un po 'complicato se non vuoi ripetere l'intera lista e vuoi solo scambiare due valori.

Opzione 2. Includere un numero di versione di ordinamento nel vincolo di unicità

Invece di aggiungere una colonna Order , aggiungi due, SortOrder e SortVersion . Applica il vincolo di unicità alla combinazione di queste due colonne.

Se desideri riordinare gli articoli nell'elenco, aumenta la colonna SortVersion di uno quando salvi il nuovo valore per SortVersion .

Opzione 3. Elimina le righe e reinserirle

A seconda del framework che si sta utilizzando, questa potrebbe essere l'opzione più semplice, dal momento che i record verranno archiviati nel livello applicazione come oggetti dominio. Basta cancellarli e reinserirli nell'ordine desiderato.

Questo potrebbe non essere fattibile se non si utilizza un EF o se ci sono relazioni FK che comporterebbero una cancellazione a cascata.

Opzione 4. Memorizza la colonna "ordina per" in una tabella separata

Tecnicamente parlando questa è l'opzione più normalizzata, dal momento che l'ordinamento probabilmente non è un attributo dell'entità ma è invece un attributo della relazione tra l'entità e un'altra entità. Quindi Order viola effettivamente 3NF, che ci crediate o no ... i valori delle colonne dovrebbero dipendere dalla chiave (1NF), dall'intera chiave (2NF) e nient'altro che dalla chiave primaria (3NF), e in questo caso la colonna Order dipende dalla relazione dell'entità all'interno di un contesto più ampio. Per normalizzarlo devi avere una tabella di join.

Supponiamo che tu abbia una tabella delle fatture e una tabella line_item. Aggiungi una terza tabella, la tabella invoice_line_item che contiene InvoiceID , LineItemID e SortOrder . Se si desidera riordinare le righe, eliminarle tutte e quindi reinserirle. Ciò evita qualsiasi problema con FK o oggetti correlati, dal momento che quegli FK non sarebbero in questa terza tabella ma in una delle altre tabelle. Ciò equivale a modificare la relazione tra gli elementi pubblicitari e le fatture, quindi ha senso eliminarli e sostituirli. Ovviamente vorrai avvolgere il tutto in una transazione.

Il vincolo univoco dovrebbe essere una combinazione della colonna Ordine e una chiave esterna a una tabella padre. Userò un "Carrello della spesa" come esempio.

Table: ShoppingCartItems
- ShoppingCartId (FK to ShoppingCarts table)
- DisplayNumber (int)

In questo caso, il vincolo univoco assicurerebbe che la combinazione di ShoppingCartItems.ShoppingCartId e ShoppingCartItems.DisplayNumber sia univoca all'interno della tabella, in modo che un carrello acquisti non possa avere più di un articolo con lo stesso ordine di visualizzazione.

Uso negativo per temp

declare int @rowA = 7 
declare int @rowB = 22

update table set order = -@rowB where order =  @rowA; 
update table set order =  @rowA where order =  @rowB; 
update table set order =  @rowB where order = -@rowB;

Puoi racchiuderlo in una transazione ma molto probabilmente non otterrai conflitti

Qui è dove devi approfondire i requisiti e non preoccuparti troppo dell'implementazione. Si prende cura di se stesso. Non esiste un modo migliore per farlo.

A qualcuno importa davvero se si dispone di numeri univoci da ordinare? E se, come utente (presumendo che lo stia facendo manualmente), non mi interessa se sono tutti a zero? Se pensi che inavvertitamente includa un duplicato e presumono che l'ordine corrisponderà a quello che vedono nella loro interfaccia, puoi sempre pubblicare un avviso. Ancora, c'è un requisito per questo numero unico e ordinato come in un sistema di fatturazione (ricordo di aver lavorato su un sistema per un'azienda in Germania che non consente lacune in questi numeri)?

Se esiste un algoritmo estremamente complicato e dispendioso in termini di tempo per eseguire l'ordinamento e si desidera salvare questo valore a fini di prestazioni, perché preoccuparsi di qualsiasi vincolo nel db? Lascia che sia il codice a gestirlo.

Lavoro con un sistema che consente all'utente di impostare l'ordinamento completo sugli elenchi a discesa. L'amministratore assegna numeri e lo fai funzionare. Un trapping per duplicati non vale la pena.

Tre suggerimenti:

1. Crea sempre gli aggiornamenti in una sequenza che non causa duplicati. Ad esempio, per scambiare 2 e 3, prima aggiornare 2 con null, quindi inserire 2 per sostituire 3, quindi inserire infine 3.
2. Probabilmente avrai bisogno di una query per inserire un record nel mezzo della lista ordinata. Se si numerano i record in sequenza, sarà necessario rinumerare molti record per inserirne uno. Se si utilizza un elenco collegato, è necessario aggiornarne solo due. Per questo motivo, prevedo che l'elenco collegato sarà meno costoso in fase di esecuzione e probabilmente più semplice da codificare.
3. Penso che sia normale denominare la colonna sort_key , ordinal o forse position .

Un'implementazione open source per Active Record in Ruby potrebbe ispirarti. Leggi la fonte della gemma agisce_as_list e confrontala con queste idee.

Spesso quando si utilizza una colonna dell'ordine, ciò che è importante è l'ordine stesso, non i valori specifici. Pertanto, se si utilizza una colonna di ordine, non è necessario preoccuparsi dei numeri in essa contenuti, a condizione che siano ordinati correttamente l'uno rispetto all'altro.

Quindi, se vuoi spostare un record nell'ordine, puoi:

• incrementa tutti i valori dell'ordine dal punto di inserimento e superiori per creare uno slot aperto
• aggiorna il valore dell'ordine del record in quello spazio.

Nessun conflitto

ulteriormente illustrato. Se hai i valori dell'ordine:

1,2,3,4,5,6,7,8

e vuoi spostare l'elemento dal # 7 al # 3, per prima cosa esegui un aggiornamento come

update foo set order = order +1 where container = X and order >=3

Questo ti dà

1,2,4,5,6,7,8,9

Quindi imposti il valore dell'ordine dell'oggetto specifico che vuoi spostare

update foo set order = 3 where id = 102334

E potresti finire con

1,2,3,4,5,6,7,9

Seguirai un modello simile per altri tipi di riorganizzazioni.

Ovviamente funziona meglio quando hai a che fare con set di dimensioni ridotte o di dimensioni ridotte. Se hai 15.000.000 di record con un ordine specifico, probabilmente vorrai utilizzare uno degli altri approcci.

Parte dell'idea di base dei database relazionali in generale, è che i record non hanno un ordine "intrinseco" nel database stesso ¹ .

L'ordinamento è normalmente imposto sul risultato di una query. In questo caso, possiamo decidere su quale colonna (s) specificare l'ordine in cui vogliamo gli articoli visualizzati, e ordinare il risultato di conseguenza.

Ad esempio, potremmo essere in grado di produrre segnalazioni di ordini in base alla data / ora di ciascun ordine (ad esempio, mostrale nell'ordine in cui sono state inserite):

select ID, value, date /* , ... */ from Orders
order by ship_date

... o la data / l'ora in cui l'ordine è stato spedito,

select ID, value, date /* , ... */ from Orders
order by ship_date

... o forse decrescente per dimensione (in modo che possiamo vedere rapidamente gli ordini più grandi, indipendentemente da quando sono accaduti).

select ID, value, date /* , ... */ from Orders
order by value desc

Puoi, naturalmente, aggiungere una colonna order_by (con un altro nome se preferisci) quando / se vuoi veramente che gli ordini siano visualizzati in un ordine che è del tutto arbitrario (o almeno basato su criteri che non sono " t memorizzato nel database). In questo caso, quando vuoi riordinare, hai un paio di scelte. Uno è semplicemente non dire al database che questa colonna contiene valori unici. Un altro è semplicemente evitare la duplicazione. Per il tuo esempio, hai iniziato con i record numerati 1 e 2. Per invertirli, puoi cambiare i numeri rispettivamente su 4 e 3.

Per eseguire questa operazione a livello di codice, trovi il valore più grande attualmente nella colonna ² , quindi usa uno più grande di quello come valore di base quando riordinato. Potresti anche voler controllare il valore minimo attualmente in uso. Se è maggiore del numero di record, puoi ricominciare la numerazione da 0.

^{1. Sebbene MS SQL abbia un concetto di "indice cluster", che si riferisce direttamente all'ordine in cui sono archiviati i record, si tratta comunque di un'ottimizzazione. 2. In genere indicizzerete questa colonna, quindi questa operazione ci aspettiamo che questa operazione sia abbastanza veloce.}