tablas tabla primaria llaves foranea con compuestas compuesta clave mysql sql-server database-design

mysql - tabla - ¿Por qué todavía hay claves primarias compuestas?



tabla compuesta sql server (9)

Estoy asignado a migrar una base de datos a un ERP de clase media. El nuevo sistema usa claves primarias compuestas aquí y allá, y desde un punto de vista pragmático, ¿por qué?

En comparación con los ID autogenerados, solo puedo ver aspectos negativos;

  • Las claves externas se vuelven borrosas
  • Migración más dura o rediseños de db
  • Inflexible como cambio de negocios. (Mi auto no tiene reg.plate ..)
  • La misma integridad se logra mejor con restricciones.

Se está volviendo al concepto de diseño de claves candiatas, del que ni siquiera veo el sentido.

¿Es un hábito / artefacto de los días flojos (minimizando espacio / índices) o me falta algo?

// edit // Acaba de encontrar un buen SO-post: claves primarias compuestas frente a un único campo ID de objeto //

Del mismo modo que las funciones encapsulan un conjunto de instrucciones, o las vistas de la base de datos resumen las conexiones de la tabla base, para hacer que las claves sustitutas abstraigan el significado de la entidad en la que se colocan.

Si, por ejemplo, tiene una tabla que contiene datos del vehículo, la aplicación de un VehicleId sustituto resume lo que significa ser un vehículo desde el punto de vista de datos. Cuando hace referencia a VehicleId = 1, seguramente está hablando de un vehículo de algún tipo, pero ¿sabemos si es un Chevy Impala 2008 o un Ford F-150 1991? No. ¿Pueden cambiar los datos subyacentes de cualquier vehículo n. ° 1 en cualquier momento? Sí.

En resumen, el objetivo de las claves compuestas es utilizar la base de datos para aplicar una o más reglas comerciales. En otras palabras: proteja la integridad de sus datos.

Ex. Tiene una lista de piezas que compra a los proveedores. Podría crear su proveedor y la tabla de partes como esta:

SUPPLIER SupplierId SupplierName PART PartId PartName SupplierId

UH oh. La tabla de partes permite datos duplicados. Como usó una clave sustituta que se autogeneró, no está aplicando el hecho de que una parte de un proveedor solo se debe ingresar una vez. En su lugar, debe crear la tabla PART como esta:

PART SupplierId SupplierPartId PartName

En este ejemplo, sus piezas provienen de proveedores específicos y desea aplicar la regla: "Un único proveedor solo puede suministrar una sola pieza una vez" en la tabla PARTS. Por lo tanto, la clave compuesta. Su clave compuesta evita la entrada duplicada accidental de una parte.

Siempre puede dejar las reglas comerciales fuera de su base de datos y dejarlas en su aplicación, pero manteniendo la regla en la base de datos (a través de una clave compuesta), se asegura de que la regla comercial se aplique en todas partes, especialmente si alguna vez decide permitir múltiples aplicaciones para acceder a los datos.

La clave primaria compuesta proporciona un mejor rendimiento cuando se trata de que se utilicen como teclas foráneas en otras tablas y reduce las lecturas de la tabla ; a veces pueden ser salvavidas. Si usa claves sustitutivas, debe ir a esa tabla para obtener información clave natural.

Por ejemplo (ejemplo puro, así que no estamos hablando de diseño de DB aquí), digamos que tiene una tabla ORDER y ORDER_ITEM . Si usa ProductId y LineNumber ( UPDATE : y como Pedro mencionó OrderId o incluso mejor OrderNumber ) como clave primaria compuesta en ORDER_ITEM , en su tabla cruzada para SHIPPING , podría tener ProductId en SHIPPING_ORDERITEM . Esto puede aumentar enormemente su rendimiento si, por ejemplo, se ha quedado sin ese producto y necesita descubrir todos los productos de ese ProductId que deben enviarse sin necesidad de unirse.

Por otro lado, si usa una clave sustituta, tiene que unirse y termina con un plan de ejecución de SQL muy ineficiente donde tiene que hacer la búsqueda de marcadores en varios índices.

Obtenga more sobre la búsqueda de favoritos que utiliza claves sustitutas se convierte en un problema importante.

La respuesta muy simple es la integridad de los datos. Si los datos van a ser útiles y precisos, se supone que las claves son necesarias. Tener una "identificación autogenerada" no elimina el requisito de otras claves también. La alternativa es no hacer cumplir la singularidad y aceptar que los datos se duplicarán y casi inútilmente contienen anomalías y, como resultado, generan errores. ¿Porqué querrías eso?

Las claves primarias naturales son frágiles.

Supongamos que hemos construido un sistema en torno a un PK natural (CountryCode, PhoneNumber), y dentro de varios años necesitamos agregar Extension o cambiar el PK a una columna: Email. Si estas columnas PK se propagan a todas las tablas secundarias, esto se vuelve muy costoso.

Hace algunos años, se crearon algunos sistemas que suponían que el número de seguridad social era un PK natural y tuvieron que rediseñarse para usar identidades cuando el SSN no era único y se podía anular.

Debido a que no podemos predecir el futuro, no sabemos si más tarde algún cambio dejará obsoleto lo que solía ser un modelo perfectamente correcto y completo.

Personalmente, prefiero el uso de claves sustitutivas. Sin embargo, al unir tablas que solo consisten en los ID de otras dos tablas (para crear una relación de muchos a muchos), las claves compuestas son el camino a seguir y, por lo tanto, eliminarlas haría las cosas más difíciles.

Hay una escuela de pensamiento que las claves sustitutas siempre son malas y que si no tienes la exclusividad para grabar mediante el uso de claves naturales tienes un mal diseño. Estoy totalmente en desacuerdo con esto (si no estás almacenando SSN o algún otro valor único, te desafío a que encuentres una clave natural para una tabla de personas, por ejemplo.) Pero muchas personas sienten que es necesario para una normalización adecuada.

A veces, tener una clave compuesta reduce la necesidad de unirse a otra mesa. A veces no es así. De modo que hay momentos en que una clave compuesta puede aumentar el rendimiento y también momentos en que puede dañar el rendimiento. Si la clave es relativamente estable, puede estar bien con un rendimiento más rápido en consultas seleccionadas. Sin embargo, si es algo que está sujeto a cambios como el nombre de una compañía, podría estar en un mundo de dolor cuando la compañía A cambie su nombre y tenga que actualizar un millón de registros asociados.

No hay una talla única en el diseño de la base de datos. Hay tiempo en que las claves compuestas son útiles y las veces que son horribles. Hay momentos en que las claves sustitutas son útiles y otras veces no.

Respuesta corta: las claves externas de varias columnas se refieren naturalmente a las claves primarias de varias columnas. Todavía puede haber una columna de identificación autogenerada que es parte de la clave principal.

Respuesta filosófica: la clave principal es la identidad de la fila . Si hay un poco de información que es una parte intrínseca de la identidad de la fila (como a qué cliente pertenece el artículo ... en una wiki de varios clientes): la información debe ser parte de la clave principal.

Un ejemplo: sistema para organizar fiestas LAN

El sistema admite varias fiestas LAN con las mismas personas y organizadores que asisten de esta manera:

CREATE TABLE users ( users_id serial PRIMARY KEY, ... );

Y hay varias partes:

CREATE TABLE parties ( parties_id serial PRIMARY KEY, ... );

Pero la mayoría de las otras cosas deben llevar la información sobre a qué parte está vinculada:

CREATE TABLE ticket_types ( ticket_types_id serial, parties_id integer REFERENCES parties, name text, .... PRIMARY KEY(ticket_types_id, parties_id) );

... esto es porque queremos referirnos a las claves primarias . La clave externa en las asistencias de tabla apunta a la tabla tipo_ticket .

CREATE TABLE attendances ( attendances_id serial, parties_id integer REFERENCES parties, ticket_types_id integer, PRIMARY KEY (attendances_id, parties_id), FOREIGN KEY (ticket_types_id, parties_id) REFERENCES parties );

Se requieren claves compuestas cuando sus claves primarias no son suplentes e inherentemente, um, compuestas, es decir, se pueden dividir en varias partes no relacionadas.

Algunos ejemplos del mundo real:

  • Tablas de enlaces de muchos a muchos, en los que las claves principales están compuestas por las claves de las entidades relacionadas.

  • Aplicaciones multi-tenant cuando tenant_id es una parte de la clave principal de cada entidad y las entidades solo se pueden enlazar dentro del mismo inquilino (restringido por una clave externa).

  • Aplicaciones que procesan datos de terceros (con claves primarias ya provistas)

Tenga en cuenta que, lógicamente, todo esto se puede lograr utilizando una restricción UNIQUE (adicional a una PRIMARY KEY sustituta).

Sin embargo, hay algunas cosas específicas de implementación:

  • Algunos sistemas no permitirán que una FOREIGN KEY refiera a algo que no sea una PRIMARY KEY .

  • Algunos sistemas solo agruparían una tabla en una PRIMARY KEY , por lo tanto, hacer que el compuesto sea la PRIMARY KEY mejoraría el rendimiento de las consultas que se unen al compuesto.

Si bien prefiero las claves sustitutas, uso casos compuestos en algunos casos. La clave compuesta puede consistir total o parcialmente en campos clave suplentes.

  • Muchos a muchos se unen a las tablas. Por lo general, estos requieren una clave única en el par de claves de todos modos. En algunos casos, se pueden incluir columnas adicionales en la clave.
  • Tablas infantiles débiles. Cosas como líneas de pedido no se sostienen por sí mismas. En este caso, utilizo la clave primaria de las tablas padre (pedidos) en la tabla compuesta.

Cuando hay varias tablas débiles relacionadas con una entidad, puede ser posible eliminar una tabla del conjunto de unión cuando se consultan datos secundarios. En el caso de las tablas de nietos, es posible unirse al abuelo al nieto sin involucrar a la mesa en el medio.