Claves sustitutas vs. naturales/empresariales

Sólo algunas razones para usar claves sustitutas:

1. Estabilidad : Cambiar una clave debido a una necesidad comercial o natural afectará negativamente las tablas relacionadas. Las claves sustitutas rara vez, si alguna vez, deben cambiarse porque no hay un significado vinculado al valor.

2. Convención : Le permite tener una convención de nomenclatura de columna de clave primaria estandarizada en lugar de tener que pensar en cómo unir tablas con varios nombres para sus PK.

3. Velocidad : Dependiendo del tipo y valor de PK, una clave sustituta de un entero puede ser más pequeña, más rápida de indexar y buscar.

Parece que nadie ha dicho nada en apoyo de las claves no sustitutas (dudo en decir "naturales"). Así que aquí va ...

Un desventaja de claves sustitutas es que son sin sentido (citado como una ventaja por algunos, pero ...). Esto a veces lo obliga a unir muchas más tablas en su consulta de lo que realmente debería ser necesario. Comparar:

select sum(t.hours)
from timesheets t
where t.dept_code = 'HR'
and t.status = 'VALID'
and t.project_code = 'MYPROJECT'
and t.task = 'BUILD';

en contra:

select sum(t.hours)
from timesheets t
     join departents d on d.dept_id = t.dept_id
     join timesheet_statuses s on s.status_id = t.status_id
     join projects p on p.project_id = t.project_id
     join tasks k on k.task_id = t.task_id
where d.dept_code = 'HR'
and s.status = 'VALID'
and p.project_code = 'MYPROJECT'
and k.task_code = 'BUILD';

A menos que alguien piense seriamente lo siguiente es una buena idea:

select sum(t.hours)
from timesheets t
where t.dept_id = 34394
and t.status_id = 89    
and t.project_id = 1253
and t.task_id = 77;

"Pero" alguien dirá, "¿qué sucede cuando cambia el código de MYPROJECT o VALID o HR?" A lo que mi respuesta sería: "¿por qué usted necesita para cambiarlo? Estas no son claves "naturales" en el sentido de que algún organismo externo va a legislar que en lo sucesivo, "VÁLIDO" debe volver a codificarse como "BUENO". Solo un pequeño porcentaje de claves "naturales" realmente entran en esa categoría, como los ejemplos habituales son el SSN y el código postal. Definitivamente usaría una tecla numérica sin sentido para tablas como Persona, Dirección, pero no para todo, que por alguna razón la mayoría de las personas aquí parecen defender.

Ver también: mi respuesta a otra pregunta

Las claves sustitutas (generalmente los enteros) tienen el valor agregado de hacer que las relaciones de la tabla sean más rápidas y más económicas en el almacenamiento y la velocidad de actualización (aún mejor, las claves externas no necesitan actualizarse cuando se usan claves sustitutas, en contraste con los campos clave de negocios que cambian de vez en cuando).

La clave principal de una tabla se debe utilizar para identificar de forma única la fila, principalmente con fines de unión. Piense en una tabla de Personas: los nombres pueden cambiar, y no se garantiza que sean únicos.

Think Companies: eres una empresa feliz de Merkin que hace negocios con otras empresas en Merkia. Usted es lo suficientemente inteligente como para no usar el nombre de la compañía como la clave principal, por lo que usa la identificación de la compañía única del gobierno de Merkia en su totalidad de 10 caracteres alfanuméricos. Luego Merkia cambia las identificaciones de la compañía porque pensaron que sería una buena idea. Está bien, utiliza la función de actualizaciones en cascada de su motor db, para un cambio que no debería implicarle en primer lugar. Más adelante, su negocio se expande y ahora trabaja con una empresa en Freedonia. La identificación de la empresa freedoniana es de hasta 16 caracteres. Debe ampliar la clave principal de identificación de la compañía (también los campos de clave externa en Pedidos, Emisiones, Transferencias de Dinero, etc.), agregando un campo País en la clave principal (también en las claves externas). ¡Ay! Guerra civil en Freedonia, se divide en tres países. El nombre del país de su asociado debe cambiarse por el nuevo; Actualizaciones en cascada al rescate. Por cierto, ¿cuál es tu clave principal? (País, CompanyID) o (CompanyID, País)? El último ayuda a las uniones, el primero evita otro índice (o quizás muchos, si desea que sus pedidos se agrupen por país también).

Todo esto no es una prueba, pero una indicación de que una clave sustituta para identificar de forma única una fila para todos los usos, incluidas las operaciones de unión, es preferible a una clave comercial.

La clave sustituta NUNCA tendrá una razón para cambiar. No puedo decir lo mismo de las llaves naturales. Apellidos, correos electrónicos, números nubmer de ISBN, todos pueden cambiar un día.

Odio las llaves sustitutas en general. Solo deben usarse cuando no hay una clave natural de calidad disponible. Es bastante absurdo cuando lo piensas, pensar que agregar datos sin sentido a tu tabla podría mejorar las cosas.

Aquí están mis razones:

1. Cuando se usan claves naturales, las tablas se agrupan de la forma en que se las busca con mayor frecuencia, lo que hace que las consultas sean más rápidas.

2. Al usar claves sustitutas, debe agregar índices únicos en las columnas de claves lógicas. Todavía necesita evitar datos duplicados lógicos. Por ejemplo, no puede permitir dos Organizaciones con el mismo nombre en su tabla de Organización aunque el pk sea una columna de identificación sustituta.

3. Cuando se usan claves sustitutas como la clave primaria, queda mucho menos claro cuáles son las claves primarias naturales. Al desarrollar desea saber qué conjunto de columnas hace que la tabla sea única.

4. En una a muchas cadenas de relaciones, las cadenas lógicas de claves. Así, por ejemplo, las organizaciones tienen muchas cuentas y las cuentas tienen muchas facturas. Así que la clave lógica de Organización es OrgName. La clave lógica de Cuentas es OrgName, AccountID. La clave lógica de la factura es OrgName, AccountID, InvoiceNumber.

   Cuando se usan claves sustitutas, las cadenas de claves se truncan al tener solo una clave externa para el padre inmediato. Por ejemplo, la tabla de facturas no tiene una columna OrgName. Solo tiene una columna para el ID de cuenta. Si desea buscar facturas para una organización determinada, deberá unirse a las tablas de Organización, Cuenta y Factura. Si usa claves lógicas, puede consultar la tabla de Organización directamente.

5. El almacenamiento de valores de clave de sustitución de las tablas de búsqueda hace que las tablas se llenen con enteros sin sentido. Para ver los datos, se deben crear vistas complejas que se unan a todas las tablas de búsqueda. Una tabla de búsqueda debe contener un conjunto de valores aceptables para una columna. No debe codificarse almacenando una clave sustituta de entero en su lugar. No hay nada en las reglas de normalización que sugiera que debe almacenar un entero sustituto en lugar del valor en sí.

6. Tengo tres libros de bases de datos diferentes. Ninguno de ellos muestra usando claves sustitutas.

Quiero compartir mi experiencia con ustedes en esta guerra interminable: D sobre el dilema clave natural vs. sustituto. Creo que tanto claves sustitutas (artificiales generadas automáticamente) como claves naturales (compuestas por columnas con significado de dominio) tienen pros y contras . Entonces, dependiendo de su situación, podría ser más relevante elegir un método u otro.

Como parece que muchas personas presentan claves sustitutas como la solución casi perfecta y claves naturales como la plaga, me centraré en los otros argumentos del punto de vista:

Desventajas de las llaves sustitutas

Las claves sustitutas son:

1. Fuente de problemas de rendimiento:
   • Por lo general, se implementan usando columnas de incremento automático que significan:
     • Un viaje de ida y vuelta a la base de datos cada vez que desee obtener una nueva identificación (sé que esto se puede mejorar mediante el uso de la caché o algoritmos [seq] hilo por igual, pero aún así esos métodos tienen sus propios inconvenientes).
     • Si un día necesita mover sus datos de un esquema a otro (al menos ocurre con bastante frecuencia en mi empresa), es posible que tenga problemas de colisión de identificación. Y sí, sé que puede usar UUID, ¡pero esos últimos requieren 32 dígitos hexadecimales! (Si te importa el tamaño de la base de datos, entonces puede ser un problema).
     • Si está utilizando una secuencia para todas sus claves sustitutas, entonces, con seguridad, terminará con la contención en su base de datos.
2. Propenso a errores. Una secuencia tiene un límite de valor máximo, por lo que, como desarrollador, debe poner atención a los siguientes puntos:
   • Debe completar su secuencia (cuando se alcanza el valor máximo, vuelve a 1,2, ...).
   • Si está utilizando la secuencia como ordenamiento (a lo largo del tiempo) de sus datos, debe manejar el caso de los ciclos (la columna con Id. 1 puede ser más nueva que la fila con Id. Valor máximo - 1).
   • Asegúrese de que su código (e incluso sus interfaces de cliente que no deberían ocurrir, ya que se supone que es una identificación interna), admita los enteros de 32b/64b que utilizó para almacenar sus valores de secuencia.
3. No garantizan datos no duplicados. Siempre puede tener 2 filas con los mismos valores de columna pero con un valor generado diferente. Para mí esto esELproblema de claves sustitutas desde el punto de vista del diseño de la base de datos.
4. Más en Wikipedia ...

Mitos sobre las llaves naturales.

1. Las claves compuestas son menos ineficientes que las claves sustitutas. ¡No! Depende del motor de base de datos utilizado:
   • Oracle
   • MySQL
2. Las claves naturales no existen en la vida real. Lo siento pero existen! En la industria de la aviación, por ejemplo, el siguiente Tuple siempre será único en relación con un programado flight (aerolínea, partida, vuelo, número, operacional, Suffix). De manera más general, cuando se garantiza que un conjunto de datos comerciales es único por un determinado estándar , entonces este conjunto de datos es un candidato [bueno] de clave natural.
3. Las claves naturales "contaminan el esquema" de las tablas secundarias. Para mí esto es más un sentimiento que un problema real. Tener una clave primaria de 4 columnas de 2 bytes cada una puede ser más eficiente que una sola columna de 11 bytes. Además, las 4 columnas se pueden usar para consultar la tabla secundaria directamente (utilizando las 4 columnas en una cláusula where) sin unirse a la tabla principal.

Conclusión

Use claves naturales cuando sea relevante para hacerlo y use claves sustitutas cuando sea mejor usarlas.

Espero que esto haya ayudado a alguien!

Siempre use una llave que no tenga significado comercial. Es sólo una buena práctica.

EDIT: estaba tratando de encontrar un enlace a él en línea, pero no pude. Sin embargo, en 'Patterns of Enterprise Archtecture' [Fowler] tiene una buena explicación de por qué no debe usar otra cosa que no sea una clave sin otro significado que no sea una clave. Se reduce al hecho de que debe tener un solo trabajo y un solo trabajo.

Las claves sustitutas son bastante útiles si planea usar una herramienta ORM para manejar/generar sus clases de datos. Si bien puede usar claves compuestas con algunos de los mapeadores más avanzados (lea: hibernación), agrega algo de complejidad a su código.

(Por supuesto, los puristas de la base de datos argumentarán que incluso la noción de una clave sustituta es una abominación).

Soy un fan de usar uids para claves sustitutas cuando sea apropiado. La gran victoria con ellos es que conoce la clave de antemano, por ejemplo. puede crear una instancia de una clase con el ID ya establecido y con garantía de ser único, mientras que con, digamos, una tecla de número entero, deberá predeterminar 0 o -1 y actualizar a un valor apropiado cuando guarde/actualice.

Los UID tienen penalizaciones en términos de búsqueda y velocidad de unión, por lo que depende de la aplicación en cuestión si son deseables.

En mi opinión, usar una clave sustituta es mejor, ya que no hay ninguna posibilidad de que cambie. Casi cualquier cosa que pueda pensar que podría usar como una clave natural podría cambiar (exención de responsabilidad: no siempre es cierto, pero comúnmente).

Un ejemplo podría ser una base de datos de automóviles: a primera vista, podría pensar que la placa podría usarse como la llave. Pero estos podrían ser cambiados por lo que sería una mala idea. Realmente no querrá descubrir eso después de lanzar la aplicación, cuando alguien se acerque a usted para saber por qué no pueden cambiar su placa de matrícula por una nueva y brillante personalizada.

Siempre use una sola columna, clave sustituta si es posible. Esto hace que las uniones, así como las inserciones/actualizaciones/eliminaciones sean mucho más limpias porque usted solo es responsable de rastrear una sola información para mantener el registro.

Luego, según sea necesario, apile las claves de su negocio como contraints o índices únicos. Esto mantendrá intacta su integridad de datos.

La lógica de negocios/las claves naturales pueden cambiar, pero la clave física de una tabla NUNCA debe cambiar.

En un escenario de datawarehouse, creo que es mejor seguir la ruta de la clave sustituta. Dos razones:

• Usted es independiente del sistema de origen, y los cambios allí, como el cambio de tipo de datos, no lo afectarán.
• Su DW necesitará menos espacio físico ya que solo usará tipos de datos enteros para sus claves sustitutas. También sus índices funcionarán mejor.

Este es uno de esos casos en los que una clave sustituta casi siempre tiene sentido. Hay casos en los que elige lo mejor para la base de datos o lo mejor para su modelo de objeto, pero en ambos casos, usar una clave sin sentido o GUID es una mejor idea. Hace que la indexación sea más fácil y más rápida, y es una identidad para su objeto que no cambia.

Como recordatorio, no es una buena práctica colocar índices agrupados en claves sustitutas aleatorias, es decir, GUID que leen XY8D7-DFD8S, ya que SQL Server no tiene la capacidad de ordenar físicamente estos datos. En su lugar, debe colocar índices únicos en estos datos, aunque también puede ser beneficioso ejecutar simplemente el analizador de SQL para las operaciones de la tabla principal y luego colocar esos datos en el Asesor de optimización del motor de base de datos.

Consulte el subproceso @ http://social.msdn.Microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

CASO 1:Su tabla es una tabla de búsqueda con menos de 50 tipos (inserciones)

Utilice teclas de negocio/naturales. Por ejemplo:

Table: JOB with 50 inserts
CODE (primary key)       NAME               DESCRIPTION
PRG                      PROGRAMMER         A programmer is writing code
MNG                      MANAGER            A manager is doing whatever
CLN                      CLEANER            A cleaner cleans
...............
joined with
Table: PEOPLE with 100000 inserts

foreign key JOBCODE in table PEOPLE
looks at
primary key CODE in table JOB

CASO 2:Su tabla es una tabla con miles de inserciones

Utilice claves sustitutas/autoincremento. Por ejemplo:

Table: ASSIGNMENT with 1000000 inserts
joined with
Table: PEOPLE with 100000 inserts

foreign key PEOPLEID in table ASSIGNMENT
looks at
primary key ID in table PEOPLE (autoincrement)

En el primer caso:

• Puede seleccionar todos los programadores en la tabla PERSONAS sin utilizar la combinación con la tabla TRABAJO, pero solo con: "SELECCIONAR * DE PERSONAS DONDE JOBCODE = 'PRG'"

En el segundo caso:

• Las consultas de la base de datos son más rápidas porque su clave principal es un entero
• No es necesario que se moleste en encontrar la siguiente clave exclusiva porque la base de datos en sí le brinda el próximo autoincremento.

Las claves sustitutas pueden ser útiles cuando la información comercial puede cambiar o ser idéntica. Los nombres comerciales no tienen que ser únicos en todo el país, después de todo. Supongamos que tratas con dos empresas llamadas Smith Electronics, una en Kansas y otra en Michigan. Puedes distinguirlos por dirección, pero eso cambiará. Incluso el estado puede cambiar; ¿Qué pasa si Smith Electronics de Kansas City, Kansas se muda a través del río a Kansas City, Missouri? No hay una forma obvia de mantener estas empresas distintas con información clave natural, por lo que una clave sustituta es muy útil.

Piense en la clave sustituta como un número ISBN. Por lo general, usted identifica un libro por título y autor. Sin embargo, tengo dos libros titulados "Pearl Harbor" de H. P. Willmott, y definitivamente son libros diferentes, no solo ediciones diferentes. En un caso como ese, podría referirme al aspecto de los libros, o el anterior frente al posterior, pero es mejor que tenga el ISBN al cual recurrir.

Caballo para los cursos. Para establecer mi sesgo; Primero soy un desarrollador, por lo que me preocupa principalmente brindarles a los usuarios una aplicación que funcione.

He trabajado en sistemas con claves naturales, y tuve que pasar mucho tiempo asegurándome de que los cambios de valor se producirían.

He trabajado en sistemas con solo claves sustitutas, y el único inconveniente ha sido la falta de datos desnormalizados para la partición.

A la mayoría de los desarrolladores de PL/SQL con los que he trabajado no les gustaban las claves sustitutas debido a la cantidad de tablas por combinación, pero nuestras bases de datos de prueba y producción nunca hicieron sudar; Las combinaciones adicionales no afectaron el rendimiento de la aplicación. Con los dialectos de la base de datos que no admiten cláusulas como "X unión interna Y en Xa = Yb", o desarrolladores que no usan esa sintaxis, las combinaciones adicionales para claves sustitutas hacen que las consultas sean más difíciles de leer, y más largas para escribir y escribir. Compruebe: ver @ publicación de Tony Andrews. Pero si usa un ORM o cualquier otro marco de generación de SQL, no lo notará. La mecanografía al tacto también mitiga.

Tal vez no sea del todo relevante para este tema, pero sí un dolor de cabeza al tratar con claves sustitutas. Los análisis pre-entregados de Oracle crean SKs generados automáticamente en todas sus tablas de dimensión en el almacén, y también almacenan aquellos en los hechos. Por lo tanto, cada vez que se deben volver a cargar (dimensiones) a medida que se agregan nuevas columnas o se deben rellenar para todos los elementos de la dimensión, los SK asignados durante la actualización hacen que los SK no estén sincronizados con los valores originales almacenados en el hecho, forzando una recarga completa de todas las tablas de datos que se unen a ella. Preferiría que incluso si el SK fuera un número sin sentido, habría alguna forma de que no pudiera cambiar para los registros originales/antiguos. Como muchos saben, fuera de la caja rara vez satisface las necesidades de una organización, y tenemos que personalizarla constantemente. Ahora tenemos 3 años de datos en nuestro almacén, y las recargas completas de los sistemas de Oracle Financial son muy grandes. Por lo tanto, en mi caso, no se generan a partir de la entrada de datos, sino que se agregan en un almacén para ayudar a informar el rendimiento. Lo entiendo, pero lo nuestro cambia, y es una pesadilla.

En el caso de la base de datos de punto en el tiempo, es mejor tener una combinación de claves sustitutas y naturales. p.ej. necesitas rastrear la información de un miembro para un club. Algunos atributos de un miembro nunca cambian. Por ejemplo, fecha de nacimiento pero el nombre puede cambiar. Así que cree una tabla de miembros con una clave sustituta de member_id y tenga una columna para DOB. Cree otra tabla llamada nombre de la persona y tenga columnas para member_id, member_fname, member_lname, date_updated. En esta tabla, la clave natural sería member_id + date_updated.