Gestione dei dati univoci in modo corretto in SQL

Question

Gestione dei dati univoci in modo corretto in SQL

#1 da (2 voti)
#2 da (1 voti)

0

Qui in Brasile abbiamo un identificativo personale chiamato CPF . Questo identificatore ha una sequenza di 11 cifre ed è unico per ogni cittadino brasiliano. Forse in altri paesi c'è qualcosa di simile.

Come è un identificatore univoco, ho scelto di impostarlo nel database come UNICA CHIAVE . E ora che le cose si complicano. Un utente potrebbe essere registrato da un amministratore e rimosso da qualche tipo di problema. Tuttavia, non viene effettivamente rimosso, ma nascosto da una bandiera cancellata. E in un altro momento, potrebbe essere ricreato di nuovo (è necessario ricrearlo e non riattivarlo).

Poiché si tratta di un identificatore individuale, il CPF verrebbe riutilizzato su un utente ricreato e sarebbe in conflitto con UNIQUE KEY.

In questa situazione, ho pensato ad alcune soluzioni pratiche per risolvere il problema così come i suoi svantaggi. Ma mi piace qual è il modo più appropriato per risolvere questo tipo di problema.

Non utilizzare un CHIAVE UNICO. Lo svantaggio è che potrebbe aumentare il tempo di interrogazione, che potrebbe essere risolto da una colonna solo KEY.
Imposta la colonna su NULL su pseudo-DELETE. Lo svantaggio è che perdo le informazioni, tuttavia, potrei risolvere semplicemente creando una seconda colonna per memorizzare il suo valore prima di essere annullata.
La UNIQUE KEY potrebbe raggruppare la colonna con una colonna di rimozione della data (NULL per impostazione predefinita). Lo svantaggio consiste nel dover utilizzare due colonne per ogni CHIAVE UNICO del gruppo.
Crea una seconda tabella per archiviare i dati rimossi senza UNIQUE KEY. Gli svantaggi qui sono quasi incalcolabili!
Rimuovi la riga, anziché nasconderlo. Lo svantaggio qui è che potrei perdere i riferimenti in un FK dell'utente.

Attualmente preferisco la prima opzione e, se necessario, opterei per la seconda o terza opzione. Ma mi chiedo se c'è un modo più corretto, seguendo il concetto più accettato dalla comunità.

sql schema

posta David Rodrigues 20.07.2014 - 18:55

fonte

2 risposte

Leggi altre domande sui tag sql schema

Come garantire oggetti unici per uguaglianza? Tempo di esecuzione di semplici cicli for

score 2 · Answer 1

ciò che hai descritto è una chiave univoca, solo che la stai pensando come una colonna unica.

Puoi creare un vincolo univoco su qualsiasi numero di colonne in una tabella, quindi qui combinerai il CPF con la colonna cancellata (o qualche altra colonna se potresti avere più record eliminati).

Ad esempio :

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

score 1 · Answer 2

Non farlo, non farlo, NON abusare del tuo database. Una chiave unica o primaria dovrebbe essere unica, senza eccezioni. Anche se utilizzi un sistema di database arcaico come xBase che supporta in modo nativo l'idea di "contrassegnare un record come eliminato", è solo una cattiva idea.

Tuttavia ci sono due opzioni buone e semplici.

1: non "elimina", "contrassegna inattivo" invece

Come verbo SQL, "cancella" ha un significato speciale in qualsiasi RDMS o sistema. Significa che stai dicendo al sistema di rimuovere e distruggere fisicamente i record. Tutti i dati che possono essere recuperati sono solo una scorciatoia del RDMS e il tuo modello di dati non dovrebbe dipendere da quel comportamento.

Con un semplice valore booleano "Attivo" o "Inattivo", dovrebbe essere facile contrassegnare il tuo record person e relativamente semplice per il tuo DBA per recuperare qualcuno che dovevano aggiustare manualmente per qualche motivo. (Non che tale sia una buona pratica, ma questo è un altro argomento.) Non muck-about con duplicati CPF; dichiara la cosa unica e istruisci il tuo personale amministrativo su come recuperare una persona inattiva.

Naturalmente, questo metodo, anche se semplice, impedisce anche la possibilità di dare facilmente a una persona una nuova chiave surrogata mantenendo la cronologia passata. Se questo è importante, vai con l'opzione # 2.

2: utilizza una tabella separata per CPF e "personID" è una proprietà del CPF anziché dell'inverso.

Il modello ovvio per la progettazione di un sistema con le persone e il loro numero di governo univoco consiste nell'avere detto numero come un campo nella tabella person .

CREATE TABLE persons (
  personID int IDENTITY PRIMARY KEY,
  name varchar(250),
  CPF varchar(10)
)

Tuttavia, in un'istanza in cui il tuo progetto richiede che due person di record possano esistere per lo stesso CPF , il design semplice è che i CPF siano unici e li facciano puntare al loro record di persona corrente.

CREATE TABLE persons (
  personID int IDENTITY PRIMARY KEY,
  name varchar(250)
)

CREATE TABLE cpfs (
  CPF varchar(10) NOT NULL PRIMARY KEY,
  personID int REFERENCES person(personID)
)

Dai la tabella sopra, è semplice creare una vista che elenchi solo la persona "reale" per ogni CPF, insieme al loro personID.

SELECT C.CPF, P.* 
FROM cfps C 
INNER JOIN persons P
  ON C.personID = P.personID

E allo stesso modo diventa semplice interrogare solo per persone attive.

SELECT *
FROM someTable
WHERE personID IN (SELECT personID FROM cfps)

Ora, questo sistema introduce una seconda tabella e include un modesto bit di complessità. Se non ti aspetti che più record di "persone" rimangano come una cosa normale e aspetti solo alcune eccezioni, vai con l'opzione n. 1.