Devo utilizzare più chiavi primarie di colonna o aggiungere una nuova colonna?

Di solito quando si ha una tabella con una chiave primaria multi-colonna, è il risultato di una tabella di join (molti-a-molti) che diventa elevata per essere la propria entità (e quindi merita la propria chiave primaria). Ci sono molti che sostengono che qualsiasi tabella di join DOVREBBE essere un'entità per impostazione predefinita, ma questa è una discussione per un altro giorno.

Diamo un'occhiata a un'ipotetica relazione da molti a molti:

Studente * --- * Classe

(uno studente può essere in più classi, una classe può avere più studenti).

Tra queste due tabelle ci sarà una tabella di giunzione chiamata StudentClass (o ClassStudent a seconda di come la scrivi). A volte, vuoi tenere traccia di cose come quando lo studente era in classe. Quindi lo aggiungerai alla tabella StudentClass. A questo punto, StudentClass è diventato un'entità unica ... e dovrebbe essere assegnato un nome per riconoscerlo come tale, ad es. Iscrizione.

Studente 1 --- * Iscrizione * --- 1 Classe

(uno studente può avere più iscrizioni, ogni iscrizione è per una classe (o andare nella direzione opposta a una classe può avere più iscrizioni, ogni iscrizione è per uno studente).

Ora puoi interrogare cose come, quanti studenti sono stati arruolati nella classe Chemistry 101 l'anno scorso? O a quali classi è stato iscritto lo studente John Doe mentre frequentava la Acme University? Questo era possibile senza la chiave primaria separata, ma una volta che hai una chiave primaria per l'iscrizione, una query più semplice sarebbe di queste iscrizioni (per id), quanti studenti hanno ricevuto un voto?

La determinazione di se un'entità merita un PK si riduce a quanta query (o manipolazione) si faranno per quell'entità. Diciamo per esempio che volevi allegare i compiti completati per uno studente in una classe. Il luogo logico per collegare questa entità (assegnazione) si trova nell'entità Registrazione. Dando la registrazione la propria chiave primaria renderebbe più semplici le query di assegnazione.

Ha senso avere una colonna id separata. Quando vuoi ottenere qualcosa dalla tabella del tuo database, è più facile da fare:

SELECT whatever FROM table WHERE id=13

di     SELEZIONA qualunque cosa FROM table WHERE col1 = 'val1' AND col2 = 'val2' AND col3 = 'val3'

Ad esempio, in un'applicazione web si traduce in un URL simile a questo:

www.somewebsite.com/somepage.php?id=13

o così:

www.somewebsite.com/somepage.php?col1=val1&col2=val2&col3=val3

Fondamentalmente stai chiedendo se dovresti usare le chiavi surrogate o naturali (nel tuo caso sembra che siano le composite chiavi naturali). Ecco un ottimo articolo: link

Preferisco le chiavi surrogate perché semplificano l'amministrazione per tutta la durata del DB (non devi mai preoccuparti dell'implicazione delle chiavi che cambiano significato, il che non dovrebbe mai accadere ma lo fa in qualsiasi sistema reale in cui siano coinvolti gli umani). Tuttavia , se ci sono molte tabelle di "ricerca" nel DB (cioè tabelle che sono fondamentalmente chiave: coppie di valori), allora le chiavi surrogate possono diventare ingombranti perché devi unirle a quelle tabelle nella query per ottenere risultati significativi.

Ad esempio, supponiamo che tu abbia due entità: Indirizzo e Paese.

• La relazione è: Indirizzo * ----- 1 Paese
• L'entità Paese è fondamentalmente una chiave: coppia di valori (ad esempio Stati Uniti: Stati Uniti, CA: Canada, MX: Messico, ecc.)
• Per interrogare questa struttura per tutti gli indirizzi negli Stati Uniti:

select * from Address where CountryCode = 'US'

• Per eseguire la stessa query con le chiavi surrogate:

select Address.* from Address join Country on Address.CountryID = Country.ID where Country.Code = 'US'

Mi sento a mio agio mandando le chiavi naturali per le tabelle di ricerca e le chiavi sostitutive per tutto il resto, se sono abbastanza sicuro che le chiavi naturali non cambieranno troppo spesso, se mai.

Dipende da come accedi ai dati. Se si eseguono molte ricerche a chiave parziale (in cui si selezionano i record in base a solo due dei tre tasti), è necessario mantenere le chiavi a più parti. OTOH, se hai molte relazioni 1: 1 con altre tabelle, probabilmente ha più senso avere una chiave surrogata.

Mi piace avere sempre una chiave primaria surrogata per ogni tabella. Ma non ci sono molti motivi "difficili" per far rispettare ciò che ho sentito.

L'unica volta in cui ho avuto a che fare con una chiave naturale a più colonne mi ha morso con ORM. Occasionalmente avrei problemi con una chiave primaria a più colonne usando Linq To Entities.

Mai dire mai, ma unirsi a 4 colonne è un dolore. Più colonne hai con i dati intelligenti, maggiori sono le possibilità che quei valori possano cambiare. I database possono essere configurati per mantenere l'integrità referenziale con gli aggiornamenti a cascata.

Puoi sempre creare un altro indice per gestire i valori univoci.

La prestazione è probabilmente trascurabile nella maggior parte dei casi, ma puoi testare le tue query con e senza la chiave surragate.

Trovo difficile trovare una buona ragione per mandare una chiave separata, ma come hai detto molte persone lo hanno inserito.

Non trovo questo aiuto (specialmente con lo spazio di archiviazione) quando si tratta di tabelle fact / detail. Esempio canonico una tabella dei fatti di vendita con una chiave (customer_key, store_key, product_key) con quantità non ha molto senso avere una chiave a livello di record.

Avere PK come autoincremento int riduce la seccatura se si scopre che la propria chiave composita può avere duplicati.

C'è una buona discussione che risale al 2002 su Chiedi a Tom . È specifico per Oracle, ma la discussione più ampia è rilevante indipendentemente dal database che stai utilizzando.