Le tabelle del database devono avere ID?

Praticamente ogni tavolo ha bisogno di una chiave primaria. Sostengo fermamente che ogni tabella ha bisogno di una chiave primaria, ma sono disposta a fare un'eccezione occasionale.

Se la chiave primaria di una tabella dovrebbe essere una chiave primaria "naturale "-- alcune colonne o colonne che fanno parte dei dati aziendali che sono naturalmente unici-- o se una chiave primaria" sintetica "dovrebbe essere usata-- alcuni dati aggiuntivi che non hanno alcun significato commerciale e sono usati esclusivamente come identificatore, in genere un numero intero incrementale o un GUID, sono un po 'un dibattito religioso. Personalmente, tendo a preferire i tasti sintetici rispetto alle chiavi naturali, ma altri modellatori di dati che ho un grande rispetto per preferire le chiavi naturali.

Nel tuo caso, uno dei problemi principali legati all'utilizzo di un indirizzo email come chiave naturale riguarda ciò che accade quando qualcuno vuole cambiare il proprio indirizzo email. Se l'indirizzo email è la chiave primaria, sarà necessario modificare i dati nella tabella USER , ma dovrai anche rippare la modifica attraverso ogni tabella figlio che ha una relazione di chiave esterna con la tabella USER . A seconda del database, questo può variare da fastidioso a un'impresa maggiore, a seconda che il database possa supportare aggiornamenti a cascata. Oracle, ad esempio, crede che le chiavi primarie debbano essere immutabili, quindi non supporta gli aggiornamenti a cascata: dovresti scrivere tu stesso quel codice (o sfruttare uno dei pacchetti in giro per farlo). Credo che MySQL supporti gli aggiornamenti in cascata, quindi devi semplicemente assicurarti che ogni vincolo di chiave esterna nel sistema sia impostato in cascata sull'aggiornamento e prova che non hai infranto la possibilità di cambiare un indirizzo email ogni volta che aggiungi un nuovo tabella al database o modificare un vincolo. D'altra parte, se utilizzi una semplice chiave sintetica, ad esempio una colonna user_id , puoi semplicemente aggiornare la colonna dell'indirizzo email come se avessi aggiornato qualsiasi altra informazione aziendale.

Qualunque cosa tu definisca come la chiave primaria della tua tabella genitrice dovrebbe essere usata come chiave esterna nella tabella figlia.

email is unique and not null, so it could be used as an index, right?

destro. Sarebbe la chiave naturale della tabella. Non hai bisogno di una chiave surrogata, ma dover partecipare a questo campo è uno spreco e potrebbe essere più lento rispetto all'utilizzo di una chiave surrogata int.

So the relationship should be 1 to 1, right? and I wouldn't need the IDs?

Diciamo che è 1 a 1. Hai ancora bisogno di collegare le due tabelle insieme.

L'uso di una chiave surrogata può aiutare con le prestazioni. Se la chiave naturale è anche di un tipo di dati che porta ad una memorizzazione ed esecuzione efficienti, tanto meglio.

In ogni schema di database ci potrebbero essere molte tabelle.

1. In alcune tabelle la chiave naturale è una buona chiave.
2. In alcune tabelle non esiste alcuna chiave naturale o non è una buona chiave.

Nel caso 1 dovresti utilizzare la chiave naturale .

In caso 2 dovresti utilizzare una chiave

"Ogni PK deve essere naturale" è un approccio sbagliato e impossibile da raggiungere. Questa è una posizione estrema , ma nessuno la propone.

"Ogni PK deve essere surrogato" è un approccio sbagliato, ma purtroppo realizzabile. Questa è anche una posizione estrema.

Usa naturale quando si adatta allo stato naturale e usa surrogato quando non lo fa .

Il "ogni PK deve essere surrogato" l'approccio porta a molti mal di testa:

• La migrazione dei dati da un database a un altro è un incubo poiché i surrogati sono sequenze che non sono sincronizzate tra i database.
• I dati sono significativi solo se visualizzati attraverso l'applicazione "the", assumendo un "un paradigma di applicazione - > un database"
• Le query sono più complesse perché devi unirti a tutte le tabelle dalla tabella più in alto (solo dove è presente la chiave aziendale, fino all'ultima tabella).
• Gli uomini d'affari parlano gli affari , sanno che una targa di auto è ADFG 237, non sanno che l'auto ha l'ID 155201 nel tavolo dell'auto. Gli incontri con gli utenti / gli uomini d'affari diventano scomodi perché continui a parlare di chiavi che non conoscono e continuano a parlare delle chiavi aziendali che conoscono.
• Gli utenti continueranno a utilizzare la chiave naturale per le ricerche, ovvero dovrai comunque mantenere la chiave naturale indicizzata.
• Interoperation con qualsiasi sistema esterno ha il sovraccarico di tradurre la chiave aziendale, come il codice LHR ISO / universaly accettato per London Heatrow Airport, nella chiave senza significato surrogato.

Chi difende il "ogni PK deve essere surrogato" l'approccio di solito discute rispetto ai caratteri è inefficiente per i join. Beh, forse era un decennio fa, e loro non riescono a produrre numeri per supportarlo.

Inoltre sostengono il problema di aggiornamento quando la chiave naturale cambia. Se la chiave naturale cambia frequentemente, allora è non una buona chiave . Se cambia ogni dieci anni , questo è ciò che aggiornamento a cascata è incorporato in RDBMS moderno .

Inoltre, qualsiasi problema di prestazioni ipotetiche dell'uso delle chiavi dei caratteri può essere risolto gettandogli dei soldi.

Ma i problemi legati all'utilizzo del "ogni PK devono essere un approccio surrogato" , non possono essere risolti gettando loro denaro.

Si prega di qualcuno di produrre qualsiasi testo canonico o bibliografia che supporti il punto "ogni FK deve essere surrogato". D'altra parte, l'approccio "misto", "surrogati quando necessario" è ben documentato. E le forme normali di Codd esistono ancora.

Il mio suggerimento:

• Utilizza le chiavi naturali dove ne esiste una abbastanza buona.
• Utilizza surrogati quando non esiste una chiave naturale sufficientemente buona.

MODIFICA: alcuni dicono che "ogni PK deve essere surrogato" è una decisione di implementazione, e quindi non viola 3NF e 3NFBC, ma li include prima dall'inizio del design, nella fase concettuale, violando 3NF e 3NFBC, che è un errore che viene pagato scrivendo molto più codice del necessario.

well as my title says I have a qeustion whether an index is always needed in database tables?

È quasi sempre necessario niente , ma fondamentalmente vuoi sempre un indice.

Le chiavi naturali sono una cosa meravigliosa quando sono efficienti ed esistono. Nel mondo reale, entrambi rari. Nel tuo esempio, sarei molto riluttante a considerare l'email come unica. Gli indirizzi email possono essere riemessi. I nomi di dominio sono acquistati e venduti. Questo è particolarmente possibile se hai cancellazioni ridotte sulle tue tabelle (ad esempio, attiva un flag IsDeleted su 1 invece di emettere un'istruzione DELETE rigida).

Inoltre, come sottolineato da @Oded, l'uso di una combinazione di stringhe è raramente una chiave efficiente come l'allocazione di un intero utilizzabile.

È utile utilizzare gli ID a causa degli indici cluster. Gli ID sono interi (piccoli) e sequenziali, quindi sono l'ideale per gli indici cluster.

Inoltre è bene usare gli ID perché sono surrogati. I surrogati non cambiano mai. Un indirizzo email può cambiare causando mal di testa per aggiornare tutte le tabelle che fanno riferimento all'e-mail come chiave.

Ma tutte le tabelle non dovrebbero avere ID. La domanda si riduce a 2 cose:

1. la tabella può beneficiare di un indice cluster?
   
2. la tabella può beneficiare di una chiave surrogata?

Se la risposta a entrambe le domande è "no", non ha senso avere un ID.

Un hash chiave / valore che non cresce con i runtime non beneficia di un ID. Il tavolo è un'isola autonoma, quindi un ID non verrebbe mai utilizzato in un join. Tutti i filtri saranno sulla chiave naturale. L'ID avrebbe solo sprecato spazio. Nessun vantaggio dal clustering sull'ID, nessun vantaggio dalla surrogata.

Le tabelle di mappatura o giunzione non dovrebbero mai avere il proprio ID. Questi sono usati per implementare relazioni molti-a-molti.

Non c'è mai una situazione in cui ti unire o filtrare sull'ID di una tabella di mappatura. Tutti i join e i filtri si trovano sugli ID che vengono mappati dalla tabella di mapping. Il PK sarebbe la combinazione dei 2 ID mappati. La combinazione di 2 ID surrogati è un surrogato a sé stante.

Di solito vuoi 2 distinti indici di copertura su una tabella di mappatura. Quindi ogni ID mappato può essere il campo dominante in un indice di copertura. Un ID dedicato potrebbe solo sprecare spazio per le giunzioni.