È una cattiva pratica avere una colonna "record status" in una tabella di database?

Lo so come un "Soft Delete"; segnando semplicemente un record come "cancellato", anche se in realtà non lo è.

Is this a good practice, or a bad practice?

Dipende.
Se questo è qualcosa che i tuoi utenti hanno bisogno [molto] allora probabilmente è una buona cosa. Nella stragrande maggioranza dei casi, tuttavia, direi che aggiunge un sacco di spese generali per un piccolo beneficio.

Does it affect normalization of the data?

No, ma influenzerà l'indicizzazione di tali dati.
Assicurati di includere la colonna "eliminata" nei tuoi indici, in modo che queste righe vengano escluse il prima possibile nelle tue query.

What are the potential pitfalls?

I tuoi dati diventano un po 'più complessi. Tutto ciò che va vicino ai dati deve "sapere" su questi record extra, "non proprio là". Oppure, devi creare viste su quelle tabelle che escludono queste righe e utilizzare queste visualizzazioni, ad esempio, in Reporting Tool of Choice.

Il tuo database potrebbe aumentare di dimensioni. Se non stai davvero eliminando queste righe, sono ancora lì, occupano spazio. Questo può o non può essere un problema, specialmente dal momento che li hai inclusi nei tuoi indici, quindi lo spazio che consumano viene moltiplicato.

Is there any alternative method of achieving the same goal? (see note)

Non proprio, no.

How can you have the database enforce unique constraints on the data for a certain status only (but allow any number of duplicates for other statuses)?

Non facilmente. L'integrità referenziale dichiarativa (clausole chiave estranee) è il modo più pulito per implementare questo ed è facile per cose come gli strumenti di Reporting riprendere queste regole per determinare le relazioni tra le tabelle. Tali regole si applicano a tutti i record, indipendentemente dallo "stato" (e non c'è modo di aggirare questo).

L'alternativa è usare Trigger, frammenti di codice procedurale che rafforzano l'integrità referenziale tra le tabelle e fanno tutte le cose intelligenti e condizionali di cui hai bisogno. Ciò è positivo per il tuo caso particolare, ma la maggior parte dei vantaggi della R.I dichiarativa va fuori dalla finestra - non ci sono relazioni rilevabili [esternamente] tra le tue tabelle; questo è tutto "nascosto" nei trigger.

Why don't databases provide a "recycle bin"-like feature or table-tracking/recovery natively, so we can let interfaces delete the actual records without worry?

Perché avrebbero loro?

Questi sono, dopo tutto, database, non file system o fogli di calcolo.

Ciò che fanno, possono [fare] molto, molto bene.

Ciò che non fanno, probabilmente non ha richiesto molto.

È una pratica. Che sia buono o cattivo dipende in gran parte dalla tua applicazione e quanto comunemente avrai davvero bisogno / vuoi fare un "undelete". Sarei piuttosto dubbioso su un piano per mettere quel tipo di colonna di ogni tabella nel sistema - sembra altamente improbabile che ti preoccupare di implementare l'undelete su ogni tabella del sistema. E richiede l'implementazione - nella stragrande maggioranza dei casi, non stai annullando la rimozione di una singola riga da una singola tabella, devi attraversare le tabelle figlio annullando la cancellazione delle righe e aggiornando le tabelle correlate.

Per la maggior parte delle altre domande, dipende molto dall'implementazione. Ad esempio, Oracle fornisce diversi metodi per tenere traccia di tutte le modifiche a una tabella-- Flashback Data Archive (FDA noto anche come Total Recall) è l'approccio più recente per mantenere una cronologia completa di ogni versione di una riga e archiviazione in-database per l'implementazione il modello di cancellazione morbida. Altri database possono fornire altri modi per implementare il modello. A seconda del database e del modo in cui si implementa l'eliminazione software, ci saranno vari impatti sulle prestazioni, se e in che modo i vincoli possono essere applicati, ecc. Se stiamo parlando di Oracle, si può fare molto con gli indici basati sulle funzioni, ad esempio , in SQL Server puoi usare spesso indici filtrati per scopi simili.

È molto comune utilizzare un campo "contrassegnato per l'eliminazione" nei sistemi MRP / ERP.

Ad esempio, si potrebbe voler contrassegnare una parte o un record di inventario che non è più venduto come inattivo, ma ci sono ancora ordini in sospeso ad esso associati. Fare una vera cancellazione sul record potrebbe influenzare gli ordini che non sono ancora stati spediti, le voci contabili che non sono ancora state pubblicate, le tabelle di cronologia che non verranno costruite fino alla fine del mese, ecc. Molti sistemi non consentiranno l'eliminazione di un record a meno che non passi una serie di validazioni contro altre tabelle. Se si eliminano le eliminazioni a cascata attraverso le relazioni, una cancellazione reale può essere ancora più distruttiva.

Invece, contrassegnandolo per l'eliminazione, metti un marcatore chiaro di intent sul record e più tardi un'attività pianificata può cancellare il record se verifica che tutte le tabelle correlate non stiano più facendo riferimento a esso.

Un caso simile potrebbe essere fatto per questa funzione su una tabella clienti e altre tabelle "a lungo termine". Ha senso anche su tabelle più volatili come gli ordini, anche se il nome della bandiera potrebbe diventare qualcosa di simile a "spedito" o "annullato". Ha la stessa funzione: non cancellarlo in questo secondo, ma usarlo come un flag per il programma di eliminazione in modo che tenti di convalidare la cancellazione del record in futuro.

Come soluzione alternativa, l'uso del sourcing di eventi consente obiettivi simili senza complicare la struttura della tabella, sebbene produca codice per modificare i tuoi dati un po 'più complessi, dato che devi scrivere la modifica in un evento che può essere conservato nella cronologia di un evento. Questo ti consente quindi di ricreare il database com'era in qualsiasi momento, che può essere una funzionalità molto utile.

(Non credo che questo sia ciò che intendevi per "tabella della storia", che penso intendessi semplicemente copiare i record modificati o cancellati in un'altra tabella prima di cambiarli)

Vedo e uso frequentemente questo modello per questi casi d'uso:

• metadati in cui si desidera solo visualizzare i valori che sono in vigore oggi. Ad esempio per scegliere da un elenco di produttori di automobili in un elenco a discesa dove abilitato = 1 i valori delle tabelle per ID, VALUE, ENABLED sono 1, 'Ford', 1 e 2, 'Edsel', 0, 3, 'Toyota' , 1 dà solo le scelte di Ford e Toyota
• per un sistema di gestione dei casi in cui il paradigma è che un caso può essere solo in uno stato alla volta. In questo caso la colonna di commutazione è stata chiamata CORRENTE con valori di 0 o 1 applicati da vincoli di controllo. Quando un caso passa da uno stato all'altro, l'applicazione aggiorna il flag CURRENT del vecchio stato a 0 e il nuovo a 1

Il problema consiste nel far rispettare l'integrità dei dati se più di un'applicazione o un servizio Web sta scrivendo sulle tabelle. Come ti assicuri che per un caso ci sia solo uno stato attuale? Come sottolinea Justin Cave, ciò può essere fatto in Oracle creando un indice virtuale basato su una funzione, ma questo overhead in più per quello che inizialmente sembrava un concetto semplice.

È una buona pratica se prevedi di utilizzare i tuoi dati per i rapporti (qualsiasi applicazione abbastanza grande avrebbe bisogno di avere rapporti).

Per accelerare la tua applicazione, non dovresti lasciare che gli strumenti di reporting vengano eseguiti sul tuo database. In quanto tale, devi eseguire una copia / sincronizzazione su un altro database.

Uso recordStatus di solo due stati ACTIVE o CANCELLED in combinazione con un timestamp lastUpdatedOn . Io uso recordStatus piuttosto che status che di solito ha un significato commerciale.

Quando sincronizzo il database di report con l'applicazione, faccio un filtro su lastUpdatedOn per sapere quali sostituirò sul lato dei rapporti.

Per quanto riguarda i rapporti, non avrò i campi recordStatus o lastUpdatedOn poiché in genere non verranno segnalati. In quanto tale, quando vedo uno stato di CANCELLED , eliminerei il record dal lato del reporting in quel modo ha solo record attivi.

Questo può essere esteso ad altri tipi di negozi come archivi o backup in cui è richiesta la sincronizzazione quasi completa. Tuttavia, la segnalazione è lo scopo più comune.

Nota il tuo esempio di Approved , New , Pending NON è una buona idea mettere un campo comune in quanto ha un business che significa che dovrebbe andare solo dove ha senso per gli affari.

Come per locked, usa versionNo che fornisce un blocco ottimistico per il tuo record.

Un'altra opzione invece di recordStatus è recordActive e averla memorizzata come boolean che occupa meno spazio e meno indicizzazione, ma sarei preoccupato dei bisogni futuri che potresti non prevedere.