Queste tabelle specifiche necessitano di chiavi surrogate?

13

Sfondo

Ho queste tabelle

+-------------------------+  +------------------------+
|Airport                  |  |Country                 |
|-------------------------|  |------------------------|
|airport_code string (PK) |  |country_code string (PK)|
|address string           |  |name string             |
|name  string             |  +------------------------+
+-------------------------+

+-------------------------+
|Currency                 |
|-------------------------|
|currency_code string (PK)|
|name string              |
+-------------------------+

airport_code è IATA (International Air Transport Association) codice aeroporto , puoi vederli nelle etichette del bagaglio quando viaggi in aereo.

country_codeèil codice Paese standard ISO 3166-1 A3 , puoi vederli nelle olimpiadi.

currency_codeèil codice monetario standard a 3 caratteri IS0 417 , puoi vederli nei tabelloni dei cambi internazionali.

Domande

Questi PK naturali sono abbastanza buoni?

Sta usando gli standard del mondo rispettati, che sono accettati da interi settori abbastanza buoni per i PK?

Le tabelle hanno bisogno di surrogati, non importa cosa?

    
posta Tulains Córdova 10.07.2013 - 16:58
fonte

4 risposte

15

No, non lo fanno. Quelle chiavi sono sicuramente abbastanza buone!

Sono unici, non raramente stanno andando a cambiare, e significativi , che è un passo avanti su una chiave surrogata. Questa è praticamente la definizione di un buon PK.

Le restrizioni relative all'immutabilità dei PK e dei numeri interi non fanno parte del Modello relazionale (Codd's) o qualsiasi standard SQL (ANSI o altro).

    
risposta data 10.07.2013 - 17:14
fonte
2

Penso che bisogno sia una parola molto strong, e in senso stretto, le tabelle probabilmente non bisogno di chiavi surrogate .

Tuttavia, se fosse il mio database, probabilmente aggiungerei le chiavi surrogate comunque. Potrei non volere necessariamente che la mia progettazione di database dipenda da un gruppo di terze parti (IATA, ISO), indipendentemente da quanto siano stabili i loro standard. Oppure, potrei non voler dipendere da uno standard particolare (ci sono altri standard di codici valutari? Non lo so). Probabilmente modellerei le mie tabelle con chiavi surrogate in questo modo:

+-------------------------+  +------------------------+
|Airport                  |  |Country                 |
|-------------------------|  |------------------------|
|airport_id       int (PK)|  |country_id     int (PK) |
|iata_airport_code string |  |iso_country_code string |
|icao_airport_code string |  +------------------------+
|faa_identifier    string |  
|address           string |  
|name              string |  
+-------------------------+

+-------------------------+
|Currency                 |
|-------------------------|
|currency_id int (PK)     |
|iso_currency_code string |
|name string              |
+-------------------------+

In altre parole, a meno che quei codici standard del settore siano intrinsecamente importanti per la mia applicazione, non li userei come PK dei miei tavoli. Sono solo etichette. La maggior parte delle altre mie tabelle probabilmente avrà comunque chiavi surrogate e questa configurazione aggiungerebbe consistenza al mio modello di dati. Il costo dell'aggiunta delle chiavi surrogate è minimo.

Aggiornamento basato su alcuni commenti:

Senza conoscere il contesto delle tabelle di esempio, è impossibile sapere quanto siano importanti le cose come IATA Airport Codes per l'applicazione che utilizza il database. Ovviamente, se i codici IATA sono centralmente importanti e utilizzati in modo pervasivo all'interno dell'applicazione, potrebbe essere la decisione corretta, dopo un'analisi adeguata, di utilizzare i codici come PK della tabella.

Tuttavia, se la tabella è solo una tabella di ricerca utilizzata in alcuni angoli dell'app, l'importanza relativa dei codici IATA potrebbe non giustificare una posizione così prominente nell'infrastruttura del database. Certo, potrebbe essere necessario fare un ulteriore join in alcune query qua e là, ma questo sforzo potrebbe essere banale in confronto allo sforzo necessario per fare la ricerca per assicurarti di comprendere appieno le implicazioni di rendere i codici IATA il campo chiave primaria. In alcuni casi, non solo non mi interessa, ma non voglio preoccuparmi dei codici IATA. Il commento di @James Snell di seguito è un perfetto esempio di qualcosa che potrei non voler preoccupare di influenzare il PK dei miei tavoli.

Inoltre, la coerenza nel design è importante. Se si dispone di un database con dozzine di tabelle che dispongono tutte di chiavi surrogate coerentemente progettate e quindi di alcune tabelle di ricerca che utilizzano codici di terze parti come PK, ciò introduce un'incoerenza. Ciò non è del tutto negativo, ma richiede un'attenzione supplementare nella documentazione e tale che potrebbe non essere giustificato. Sono tabelle di ricerca per carità, solo l'uso di una chiave surrogata per coerenza è perfettamente soddisfacente.

Aggiornamento basato su ulteriori ricerche:

Ok, la curiosità mi ha morso e ho deciso di fare qualche ricerca sui codici aeroportuali IATA per divertimento, iniziando dai link forniti nella domanda.

Come risulta, i codici IATA non sono così universali e autorevoli come la domanda li rende fuori. In base a questa pagina :

Most countries use four-character ICAO codes, not IATA codes, in their official aeronautical publications.

Inoltre, i codici IATA e ICAO sono diversi dai codici identificativi FAA , che sono ancora un altro modo per identificare gli aeroporti.

Il mio punto di vista è non iniziare un dibattito su quali codici sono migliori o più universali o più autorevoli o più completi, ma per mostrare esattamente perché progettare la struttura del database attorno a un identificatore arbitrario di terze parti non è qualcosa che vorrei scegli di fare, a meno che non ci fosse un motivo aziendale specifico per farlo .

In questo caso mi sento il mio database sarebbe meglio strutturato, più stabile e più flessibile, rinunciando ai codici IATA (o a qualsiasi terza parte, codice potenzialmente modificabile) come candidato chiave principale e utilizzare una chiave surrogata. In tal modo, posso evitare qualsiasi potenziale insidia che potrebbe verificarsi a causa della selezione della chiave primaria.

    
risposta data 10.07.2013 - 17:26
fonte
1

Pur avendo chiavi surrogate sui campi va bene e non c'è niente di sbagliato in quel qualcosa da considerare potrebbe essere la dimensione della pagina dell'indice stesso.

Poiché si tratta di un database relazionale, farai molti join e avere una chiave surrogata di un tipo numerico potrebbe renderlo più facile da gestire nel database, ovvero la dimensione della pagina dell'indice sarà più piccola e quindi più veloce da cercare attraverso . Se questo è un progetto di piccole dimensioni, non ha importanza e passerai senza problemi, tuttavia quanto più grande sarà l'applicazione, tanto più vorrai ridurre i colli di bottiglia.

Avere BIGINT, INT, SMALLINT, TINYINT o qualsiasi altro tipo di dati di tipo intero potrebbe farti risparmiare problemi.

Solo i miei 2 centesimi

UPDATE:

Piccolo progetto - usato da pochi, forse anche da poche decine di persone. Piccola scala, progetto dimostrativo, progetto per uso personale, qualcosa da aggiungere a un portfolio quando si presentano le proprie competenze senza esperienza e simili.

Grande progetto - utilizzato da migliaia, decine di migliaia, milioni di utenti al giorno. Qualcosa che avresti creato per un'azienda nazionale / internazionale con una vasta base di utenti.

Di solito ciò che succede è che alcuni dei record vengono selezionati spesso e il server memorizza nella cache i risultati per un accesso rapido, ma di tanto in tanto è necessario accedere a un record meno utilizzato, a quel punto il server dovrebbe immergersi nella pagina dell'indice. (nell'esempio sopra riportato con i nomi degli aeroporti, le persone spesso volano compagnie aeree nazionali, per esempio Chichago - > Los Angeles, ma quante volte le persone volano da Boston - > Zimbabwe)

Se si utilizza VARCHAR, significa che la spaziatura non è uniforme, a meno che i dati non abbiano sempre la stessa lunghezza (a questo punto un valore CHAR è più efficace). Ciò rende la ricerca dell'indice più lenta e con il server già impegnato a gestire migliaia e migliaia di query al secondo, ora deve perdere tempo andando attraverso un indice non uniforme, e fare di nuovo la stessa cosa sui join (che è più lento di seleziona regolarmente su una tabella non ottimizzata, prendi DW come esempio dove ci sono meno join possibili per accelerare il recupero dei dati). Anche se usi UTF che può rovinare anche il motore del database (ho visto alcuni casi).

Personalmente, dalla mia esperienza personale, un indice correttamente organizzato può aumentare la velocità di un join di circa il 70% e fare un join su una colonna intera può accelerare il join di circa il 25% (a seconda di i dati). Man mano che le tabelle principali iniziano a crescere e queste tabelle vengono utilizzate su di esse, preferiresti avere un tipo di dati integer che occupa la colonna che ha pochi byte rispetto a un campo VARCHAR / CHAR che occuperà più spazio. Si tratta di risparmiare spazio su disco, aumentare le prestazioni e la struttura complessiva di un database relazionale.

Inoltre, come James Snell ha menzionato:

Primary keys must also be immutable, something IATA airport codes are definitely not. They can be changed at the whim of the IATA.

Considerando questo, preferiresti aggiornare un record associato a un numero, e dover aggiornare quel record più tutti i record nella tabella a cui ti unisci.

    
risposta data 10.07.2013 - 19:45
fonte
1

Se utilizzi l'approccio "Uso le chiavi surrogate tutto il tempo", puoi ignorare questo tipo di problema. Potrebbe non essere una buona cosa, perché è importante dare qualche pensiero ai tuoi dati, ma sicuramente risparmia molto tempo, impegno e impegno. Se qualcuno dovesse adottare un'accettazione a questa regola, gli esempi elencati certamente si qualificano perché richiede un "atto di congresso" vicino a fare il resto.

Le query ad hoc di un database con queste chiavi naturali sono certamente utili. Creare viste che facciano la stessa cosa includendo le tabelle di ricerca può funzionare altrettanto bene. I database moderni fanno un lavoro molto migliore con questo tipo di cose al punto in cui probabilmente non ha importanza.

Ci sono alcuni casi specifici negli Stati Uniti, dove gli standard sono stati drasticamente cambiati: il codice postale si è espanso da 5 a 9 cifre, le abbreviazioni di stato a 2 lettere consistenti e si è sbarazzato del periodo (Ricordare quando Illinois era Ill?), e la maggior parte del mondo ha avuto a che fare con Y2K. Se hai un'app in tempo reale con dati sparsi in tutto il mondo che contengono miliardi di record, gli aggiornamenti a cascata non sono l'idea migliore, ma non dovremmo tutti lavorare in luoghi che affrontano tali sfide? Con questo set di dati, puoi testarlo tu stesso e trovare una risposta più difficile.

    
risposta data 11.07.2013 - 16:29
fonte