Claves primarias compuestas versus campo ID de objeto único

He estado desarrollando aplicaciones de base de datos durante 15 años y aún no he encontrado un caso en el que una clave no sustitutiva fuera una mejor opción que una clave sustitutiva.

No estoy diciendo que tal caso no exista, solo estoy diciendo que cuando se tienen en cuenta los problemas prácticos de desarrollar realmente una aplicación que accede a la base de datos, generalmente los beneficios de una clave sustituta comienzan a abrumar la pureza teórica de las claves no sustitutas.

La clave primaria debe ser constante y sin sentido ; las claves no sustitutivas generalmente fallan uno o ambos requisitos, eventualmente

• Si la clave no es constante, tiene un problema de actualización futura que puede ser bastante complicado

• Si la clave no carece de sentido, entonces es más probable que cambie, es decir, que no sea constante; véase más arriba

Tomemos un ejemplo simple y común: una tabla de artículos de inventario. Puede ser tentador hacer el número de artículo (sku número, código de barras, código de parte, o lo que sea) la clave principal, pero luego un año después todos los números de artículo cambian y te quedas con un problema muy desordenado de actualización de toda la base de datos...

EDITAR: hay un tema adicional que es más práctico que filosófico. En muchos casos vas a encontrar una fila en particular de alguna manera, luego actualizarla o encontrarla de nuevo (o ambas). Con las claves compuestas hay más datos para realizar un seguimiento y más contraindicaciones en la cláusula WHERE para el re-find o actualizar (o eliminar). ¡También es posible que uno de los segmentos clave haya cambiado mientras tanto!. Con una clave sustituta, siempre hay un solo valor que retener (el ID sustituto) y, por definición, no puede cambiar, lo que simplifica significativamente la situación.

Suena como si la persona que creó la base de datos estuviera en el lado de las claves naturales del gran debate de claves naturales vs.claves sustitutas.

Nunca he oído hablar de ningún problema con btres en los campos de ID, pero tampoco lo he estudiado en gran profundidad...

Caigo en el lado de la clave sustituta: Tiene menos repetición cuando usa una clave sustituta, porque solo está repitiendo un solo valor en las otras tablas. Dado que los humanos rara vez se unen a las mesas a mano, no nos importa si es un número o no. Además, dado que solo hay una columna de tamaño fijo para buscar en el índice, es seguro asumir que los sustitutos también tienen un tiempo de búsqueda más rápido por clave primaria.

El uso de campos 'unique (object) ID' simplifica las uniones, pero debe intentar que la otra clave (posiblemente compuesta) siga siendo única NOT NO relaje las restricciones no nulas y mantenga la restricción única.

Si el DBMS no puede manejar enteros únicos de manera efectiva, tiene grandes problemas. Sin embargo, usar tanto un 'ID único (objeto)' como la otra clave usa más espacio (para los índices) que solo la otra clave, y tiene dos índices para actualizar en cada operación de inserción. Así que no es un gratis but pero mientras mantengas la llave original, también, estarás bien. Si elimina la otra clave, está rompiendo el diseño de su sistema; todo el infierno se desatará eventualmente (y puede o no detectar que el infierno se desató).

Básicamente soy miembro del equipo de subrogate key, e incluso si aprecio y entiendo argumentos como los presentados aquí por JeremyDWill, todavía estoy buscando el caso donde la clave "natural" es mejor que la subrogada ...

Otras publicaciones que tratan este tema generalmente se refieren a la teoría de bases de datos relacionales y el rendimiento de las bases de datos. Otro argumento interesante, siempre olvidado en este caso, está relacionado con normalización de tablas y productividad de código:

Cada vez que cree una tabla, perder tiempo

1. identificando su clave primaria y su características físicas (tipo, tamaño)
2. recordar estas características cada vez que quiero hacer referencia a ella en mi código?
3. explicando mi elección PK a otros desarrolladores en el equipo?

Mi respuesta es no a todas estas preguntas:

1. No tengo tiempo que perder tratando de identificar "la mejor Clave Primaria" cuando tratar con una lista de persona.
2. No quiero recordar que la Clave primaria de mi tabla" computer" es una cadena larga de 64 caracteres (hace Windows acepta que muchos caracteres ¿por un nombre de computadora?).
3. No quiero explicar mi elección a otros desarrolladores, donde uno de ellos finalmente dirá " Sí hombre, pero tenga en cuenta que usted tiene que manejar computadoras en diferentes dominios? Permite esta cadena de 64 caracteres almacenar el nombre de dominio + el ¿nombre de la computadora?".

Así que he estado trabajando durante los últimos cinco años con una regla muy básica: cada tabla (llamémosla 'myTable') tiene su primer campo llamado 'id_MyTable' que es de tipo uniqueIdentifier. Incluso si esta tabla soporta una relación "muchos-a-muchos", como una tabla 'ComputerUser', donde la combinación de 'id_Computer' y 'id_User' forma una Clave Primaria muy aceptable, prefiero crear este campo 'id_ComputerUser' siendo un uniqueIdentifier, solo para atenerme a la regla.

La principal ventaja es que no tiene que preocuparse por el uso de animore Clave Principal y / o Clave Externa dentro de su código. Una vez que tenga el nombre de la tabla, sabrá el nombre y tipo de PK. Una vez que sepa qué enlaces se implementan en su modelo de datos, sabrá el nombre de las claves foráneas disponibles en la tabla.

No estoy seguro de que mi regla sea la mejor. Pero es muy eficiente!

Usar teclas naturales hace una pesadilla usando cualquier automatic automático como capa de persistencia. Además, las claves foráneas en varias columnas tienden a superponerse entre sí y esto dará más problemas al navegar y actualizar la relación de una manera OO.

Aún así, podría transformar la clave natural en una restricción única y agregar un id generado automáticamente; esto no elimina el problema con las claves foráneas, sin embargo, tendrán que cambiarse a mano; esperemos que varias columnas y las restricciones superpuestas serán una minoría de toda la relación, por lo que podría concentrarse en refactorizar donde más importa.

Pk natural tienen su motivación y usos escenario y no son una mala cosa(tm), simplemente tienden a no llevarse bien con OR.

Mi sensación es que, como cualquier otro concepto, las claves naturales y la normalización de la tabla deben usarse cuando sean sensatas y no como restricciones de diseño ciegas

Voy a ser breve y dulce aquí: las claves primarias compuestas no son buenas en estos días. Agregue claves arbitrarias sustitutas si puede y mantenga los esquemas de claves actuales a través de restricciones únicas. OR es feliz, tú eres feliz, programador original no tan feliz, pero a menos que sea tu jefe, entonces puede lidiar con ello.

Un enfoque práctico para desarrollar una nueva arquitectura es el que utiliza claves sustitutas para tablas que contendrán miles de registros de varias columnas altamente únicos y claves compuestas para tablas descriptivas cortas. Por lo general, encuentro que las universidades dictan el uso de claves sustitutas, mientras que los programadores del mundo real prefieren las claves compuestas. Realmente necesita aplicar el tipo correcto de clave primaria a la tabla, no solo de una manera u otra.

Las claves compuestas pueden ser buenas - pueden afectar el rendimiento - pero no son la única respuesta, de la misma manera que una clave única (sustituta) no es la única respuesta.

Lo que me preocupa es la vaguedad en el razonamiento para elegir claves compuestas. La mayoría de las veces, la vaguedad sobre algo técnico indica una falta de comprensión, tal vez siguiendo las pautas de otra persona, en un libro o artículo....

No hay nada malo con un único ID único-de hecho si tienes una aplicación conectada a un servidor de base de datos y puedes elegir qué base de datos estás usando, todo será bueno, y puedes hacer prácticamente cualquier cosa con tus claves y no sufrir demasiado.

Se ha escrito y se escribirá mucho sobre esto, porque no hay una sola respuesta. Hay métodos y enfoques que deben aplicarse cuidadosamente y de manera experta.

He tenido muchos problemas con los identificadores proporcionados automáticamente por la base de datos, y evítelos siempre que sea posible, pero utilícelos de vez en cuando.

... cómo la base de datos maneja los campos de identificación de una manera no eficiente y cuando está construyendo índices, las clases de árbol son defectuosas ...

Esto fue casi sin duda una tontería, pero puede estar relacionado con el problema de la contención de bloques de índice al asignar números incrementales a un PK a una tasa alta desde diferentes sesiones. Si es así, entonces el índice DE CLAVE INVERSA está ahí para ayudar, aunque a expensas de un tamaño de índice más grande debido a un cambio en el algoritmo de división de bloques. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref998

Ir sintético, sobre todo si ayuda a un desarrollo más rápido con su conjunto de herramientas.

No soy un experimentado, pero todavía estoy a favor de Usar la clave primaria como id aquí está la explicación usando un ejemplo..

El formato de los datos externos puede cambiar con el tiempo. Por ejemplo, podría pensar que el ISBN de un libro sería una buena clave primaria en una tabla de libros. Después de todo, los ISBN son únicos. Pero a medida que se escribe este libro en particular, la industria editorial en los Estados Unidos se está preparando para un cambio importante a medida que se agregan dígitos adicionales a todos los ISBN. Si lo hiciéramos utiliza el ISBN como la clave principal en una tabla de libros, tendríamos que actualizar cada fila para reflejar este cambio. Pero entonces tendríamos otro problema. Habrá otras tablas en la base de datos que hacen referencia a filas en la tabla books a través de la clave primaria. No podemos cambiar la clave en la tabla books a menos que primero revisemos y actualicemos todas estas referencias. Y eso implicará eliminar las restricciones de clave externa, actualizar las tablas, actualizar la tabla de libros y, finalmente, restablecer las restricciones. Todo en general, esto es una especie de dolor. Los problemas desaparecen si usamos nuestro propio valor interno como clave principal. Ningún tercero puede venir y decirnos arbitrariamente que cambiemos nuestro esquema: controlamos nuestro propio espacio de claves. Y si algo como el ISBN necesita cambiar, puede cambiar sin afectar ninguna de las relaciones existentes en la base de datos. En efecto, hemos desacoplado el tejido de las filas de la representación externa de los datos en esas filas.

Aunque el la explicación es bastante libresca, pero creo que explica las cosas de una manera más simple.

@ JeremyDWill

Gracias por proporcionar un equilibrio tan necesario al debate. En particular, gracias por la información sobre DOMAIN s.

En realidad uso claves sustitutas en todo el sistema en aras de la consistencia, pero hay compensaciones involucradas. La causa más común para mí para maldecir el uso de claves sustitutas es cuando tengo una tabla de búsqueda con una lista corta de valores canónicos-Usaría menos espacio y todas mis consultas serían más cortas / más fáciles / más rápidas si hubiera hecho los valores el PK en lugar de tener que unirse a la mesa.

Puede hacer ambas cosas - dado que cualquier base de datos de grandes empresas es probable que sea utilizada por varias aplicaciones, incluidos los DBA humanos que ejecutan consultas únicas e importaciones de datos, diseñarla puramente para el beneficio de los sistemas OR no siempre es práctico o deseable.

Lo que tiendo a hacer en estos días es agregar una propiedad "RowID" a cada tabla - este campo es un GUID, y tan único para cada fila. Esta NO es la clave principal, es una clave natural (si es posible). Sin embargo, cualquier capa OR trabajando sobre esta base de datos puede usar el RowID para identificar sus objetos derivados.

Así que usted podría tener:

CREATE TABLE dbo.Invoice (
  CustomerId varchar(10),
  CustomerOrderNo varchar(10),
  InvoiceAmount money not null,
  Comments nvarchar(4000),
  RowId uniqueidentifier not null default(newid()),

  primary key(CustomerId, CustomerOrderNo)
)

Así que su DBA está feliz, su arquitecto OR está feliz, y la integridad de su base de datos se conserva!

Solo quería agregar algo aquí que nunca veo cubierto cuando se discuten los campos de identidad enteros generados automáticamente con bases de datos relacionales (porque los veo mucho), y es decir, su tipo base puede un desbordamiento de voluntad en algún momento.

Ahora no estoy tratando de decir que esto automáticamente hace que los ID compuestos sean el camino a seguir, pero es solo una cuestión de hecho que a pesar de que se podrían agregar más datos lógicamente a una tabla (que sigue siendo única), la única identidad entera generada automáticamente podría evitar que esto suceda.

Sí, me doy cuenta de que para la mayoría de las situaciones es poco probable, y el uso de un entero de 64 bits le da un montón de espacio libre, y siendo realistas, la base de datos probablemente debería haber sido diseñado de manera diferente si alguna vez se produjo un desbordamiento como este.

Pero eso no impide que alguien lo haga... una tabla que utiliza un único entero de 32 bits generado automáticamente como identidad, que se espera que almacene todas las transacciones a nivel global para una comida rápida en particular compañía, va a fallar tan pronto como intenta insertar es 2,147,483,648 th transacción (y que es un escenario completamente factible).

Es solo algo a tener en cuenta, que la gente tiende a pasar por alto o simplemente ignorar por completo. Si alguna tabla se va a insertar con regularidad, se deben tener en cuenta la frecuencia y la cantidad de datos que se acumularán con el tiempo, y si se debe usar o no un identificador basado en enteros.