¿Estas tablas específicas necesitan claves sustitutas?

13

Fondo

Tengo estas tablas

+-------------------------+  +------------------------+
|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 es la IATA (International Air Transport Association) código del aeropuerto , puede verlas en las etiquetas de su equipaje cuando viaje en avión.

country_codeesel ISO 3166-1 A3 código de país estándar , Puedes verlos en las Olimpiadas.

currency_codeesel IS0 417 código de moneda estándar de 3 caracteres , puede verlos en tableros de anuncios de cambio de moneda internacional.

Preguntas

¿Son estas PK naturales lo suficientemente buenas?

¿El uso de estándares respetados en todo el mundo, que son aceptados por industrias completas, es lo suficientemente bueno para los PK?

¿Estas tablas necesitan sustitutos sin importar qué?

    
pregunta Tulains Córdova 10.07.2013 - 16:58

4 respuestas

15

No, no lo hacen. ¡Esas claves son definitivamente lo suficientemente buenas!

Son únicos, no rara vez van a cambiar, y significativos , que es un paso adelante sobre una clave sustituta. Esa es prácticamente la definición de un buen PK.

Las restricciones acerca de que los PK sean inmutables y enteros numéricos no forman parte del Modelo relacional (Codd's) o Cualquier estándar SQL (ANSI u otro).

    
respondido por el Bobson 10.07.2013 - 17:14
2

Creo que necesidad es una palabra muy fuerte, y en un sentido estricto, las tablas probablemente no necesitan claves sustitutas .

Sin embargo, si fuera mi base de datos, probablemente agregaría claves sustitutas de todos modos. Es posible que no necesariamente desee que el diseño de mi base de datos dependa de un grupo de terceros (IATA, ISO), independientemente de cuán estables sean sus estándares. O bien, es posible que no quiera depender de un estándar en particular (¿existen otros estándares de códigos de moneda? No lo sé). Probablemente modelaría mis tablas con claves sustitutas de este 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              |
+-------------------------+

En otras palabras, a menos que los códigos estándar de la industria sean inherentemente importantes para mi aplicación, no los usaría como el PK de mis tablas. Solo son etiquetas. La mayoría de mis otras tablas probablemente tendrán claves sustitutas de todos modos, y esta configuración agregaría consistencia a mi modelo de datos. El costo de "agregar" las claves sustitutas es mínimo.

Actualización basada en algunos de los comentarios:

Sin saber el contexto de las tablas de ejemplo, es imposible saber qué tan importantes son los códigos de aeropuerto IATA para la aplicación que utiliza la base de datos. Obviamente, si los códigos IATA son de importancia central y se utilizan de manera generalizada en toda la aplicación, podría ser una decisión correcta, después de un análisis adecuado, utilizar los códigos como el PK de la tabla.

Sin embargo, si la tabla es solo una tabla de búsqueda que se usa en algunos rincones de la aplicación, la importancia relativa de los códigos IATA puede no justificar un lugar tan prominente en la infraestructura de la base de datos. Claro, es posible que tenga que hacer una combinación adicional en algunas consultas aquí y allá, pero ese esfuerzo puede ser trivial en comparación con el esfuerzo que tomaría hacer la investigación para asegurarse de que comprende completamente las implicaciones de hacer que los códigos IATA campo de clave primaria. En algunos casos, no solo no me importa, sino que no quiero tener que preocuparme acerca de los códigos IATA. El comentario de @James Snell a continuación es un ejemplo perfecto de algo que no quiero tener que preocuparme por afectar el PK de mis tablas.

También, la consistencia en el diseño es importante. Si tiene una base de datos con docenas de tablas que tienen claves sustitutas diseñadas de manera consistente, y luego algunas tablas de búsqueda que usan códigos de terceros como PK, eso introduce una inconsistencia. Eso no es del todo malo, pero requiere atención adicional en la documentación y tal que no esté justificado. Son tablas de búsqueda por bondad, solo usar una clave sustituta para la consistencia es perfectamente correcto.

Actualización basada en investigaciones adicionales:

Ok, la curiosidad me mordió y decidí investigar un poco los códigos de aeropuertos de la IATA por diversión, comenzando con los enlaces que se proporcionan en la pregunta.

Resulta que los códigos IATA no son tan universales y fidedignos como la pregunta los hace ser. Según esta página :

  

La mayoría de los países utilizan códigos de la OACI de cuatro caracteres, no los códigos IATA, en su   Publicaciones oficiales aeronáuticas.

Además, los códigos IATA y los códigos ICAO son distintos de los códigos de identificación FAA , que son otra forma de identificar aeródromos.

Lo que quiero decir es no comenzar un debate sobre qué códigos son mejores o más universales o más autorizados o más completos, sino mostrar exactamente por qué diseñar una estructura de base de datos en torno a un identificador de terceros no es algo que yo haría elige hacer, a menos que haya una razón comercial específica para hacerlo .

En este caso, Siento mi base de datos estaría mejor estructurada, sería más estable y más flexible si renunciaba a los códigos IATA (o a cualquier tercero, código potencialmente variable) como candidato clave principal y usar una clave sustituta. Al hacerlo, puedo renunciar a cualquier trampa potencial que pueda surgir debido a la selección de la clave principal.

    
respondido por el Eric King 10.07.2013 - 17:26
1

Aunque tener claves sustitutas en los campos está bien y no hay nada de malo en que algo a considerar podría ser el tamaño del índice de la página en sí.

Ya que esta es una base de datos relacional, hará muchas uniones y tendrá una clave sustituta de tipo numérico que facilitará el manejo de la base de datos; . Si este es un proyecto pequeño, no importará y podrá sobrevivir sin problemas, sin embargo, cuanto más grande sea la aplicación, más querrá reducir los cuellos de botella.

Tener un BIGINT, INT, SMALLINT, TINYINT o cualquier otro tipo de datos de tipo entero podría ahorrarle algunos problemas en el futuro.

Solo mis 2 centavos

ACTUALIZAR :

Proyecto pequeño: utilizado por unos pocos, tal vez incluso unas pocas docenas de personas. Pequeña escala, proyecto de demostración, proyecto para uso personal, algo que se agrega a una cartera al presentar sus habilidades sin experiencia, y demás.

Proyecto grande: utilizado por miles, decenas de miles, millones de usuarios diariamente. Algo que construirías para una compañía nacional / internacional con una base de usuarios enorme.

Por lo general, lo que sucede es que se seleccionan a menudo algunos de los registros seleccionados, y el servidor almacena en caché los resultados para un acceso rápido, pero de vez en cuando debe acceder a un registro menos usado, momento en el cual el servidor tendría que sumergirse en la página de índice. (en el ejemplo anterior con los nombres de los aeropuertos, la gente a menudo vuela en aerolíneas nacionales, por ejemplo, Chichago - > Los Angeles, pero con qué frecuencia vuela la gente desde Boston - > Zimbabwe)

Si se usa VARCHAR, significa que el espaciado no es uniforme, a menos que los datos sean siempre de la misma longitud (en cuyo punto es más efectivo un valor CHAR). Esto hace que la búsqueda en el índice sea más lenta, y como el servidor ya está ocupado manejando miles y miles de consultas por segundo, ahora tiene que perder tiempo en un índice no uniforme, y hacer lo mismo de nuevo en las combinaciones (que es más lento que las selecciones regulares en una tabla no optimizada, tome DW como ejemplo donde hay la menor cantidad de combinaciones posibles para acelerar la recuperación de datos). Además, si usa UTF, también puede alterar el motor de la base de datos (he visto algunos casos).

Personalmente, desde mi propia experiencia, un índice adecuadamente organizado puede aumentar la velocidad de una unión en un ~ 70%, y hacer una unión en una columna entera puede acelerar la unión hasta en un 25% (dependiendo de los datos). A medida que las tablas principales comiencen a crecer y estas tablas se usen en ellas, preferiría que un tipo de datos entero ocupara la columna que tiene unos pocos bytes frente a un campo VARCHAR / CHAR que ocupará más espacio. Todo se reduce a ahorrar espacio en disco, aumentar el rendimiento y la estructura general de una base de datos relacional.

También, como James Snell mencionó:

  

Las claves primarias también deben ser inmutables, algo que los códigos de aeropuerto IATA definitivamente no lo son. Se pueden cambiar a capricho de la IATA.

Entonces, teniendo esto en cuenta, ¿preferiría tener que actualizar 1 registro que está vinculado a un número, en lugar de tener que actualizar ese registro más todos los registros en la tabla en la que se une?

    
respondido por el Toni Kostelac 10.07.2013 - 19:45
1

Si adopta el enfoque "Yo uso claves sustitutas todo el tiempo", puede evitar este tipo de inquietud. Puede que no sea algo bueno porque es importante que reflexionemos un poco sobre sus datos, pero sin duda ahorra mucho tiempo, energía y esfuerzo. Si alguien adoptara una aceptación de esta regla, los ejemplos enumerados ciertamente califican porque se necesita un "acto del Congreso" cercano para hacer el cambio.

Las consultas ad hoc de una base de datos con estas claves naturales son ciertamente útiles. Crear vistas que hagan lo mismo al incluir las tablas de búsqueda puede funcionar igual de bien. Las bases de datos modernas hacen un trabajo mucho mejor con este tipo de cosas hasta el punto en que probablemente no importa.

Hay algunos casos específicos en los EE. UU., donde se cambiaron los estándares drásticamente: el código postal se expandió de 5 a 9 dígitos, abreviaturas estatales a 2 letras consistentes y se deshace del período (¿Recuerda cuando Illinois estaba enfermo?), y la mayor parte del mundo tuvo que lidiar con Y2K. Si tiene una aplicación en tiempo real con datos distribuidos por todo el mundo que contienen miles de millones de registros, las actualizaciones en cascada no son la mejor idea, pero ¿no deberíamos trabajar en lugares que enfrentan tales desafíos? Con ese conjunto de datos, podría probarlo usted mismo y proponer una respuesta más definida.

    
respondido por el JeffO 11.07.2013 - 16:29

Lea otras preguntas en las etiquetas