¿Cuáles son los criterios de diseño para las claves primarias?

Elegir buenas claves primarias, claves candidatas y las claves externas que las utilizan es una tarea de diseño de base de datos de vital importancia, tanto arte como ciencia. La tarea de diseño tiene criterios de diseño muy específicos.

¿Cuáles son los criterios?

Los criterios para considerar una clave principal son:

  • Unicidad
  • Irreductibilidad (ningún subconjunto de la clave identifica de manera única una fila en la tabla)
  • Simplicidad (para que la representación relacional y la manipulación puedan ser más simples)
  • Estabilidad (no debe ser alterado con frecuencia)
  • Familiaridad (significativa para el usuario)

¿Qué es una clave principal?

La clave primaria es algo que identifica de manera única una fila / registro de datos. También puede ser varias columnas, lo que se denomina compuesto.

Capacidad de cambiar

Debido a que la clave principal se usa a menudo para referencias externas, debe ser lo más estable posible. Todos los datos en la base de datos son mutables, siempre que alguien se conecte con una cuenta que tenga los privilegios apropiados. Esta es la razón por la cual las bases de datos brindan la capacidad de definir CASCADE ON DELETE y CASCADE ON UPDATE, para sincronizar dependencias referenciales sin tener que deshabilitar las restricciones.

¿Natural o Artificial / Subrogado?

Idealmente, quieres una clave natural. Una clave natural son los datos existentes que identifican de manera única a la entidad que está modelando. Por ejemplo, las abreviaturas de los estados de EE. UU. Son una buena clave natural porque la abreviatura es coherente y todos las conocen:

US_STATE_PRIMARY_KEY US_STATE -------------------------- AL Alabama AK Alaska AZ Arizona AR Arkansas CA California 

No intentes demasiado para encontrar una llave natural. Ellos rara vez existen. Es poco probable que un nombre de Estado de EE. UU. Cambie, pero es plausible.

De manera realista, las claves primarias generalmente serán artificiales (a menudo generadas por la funcionalidad de la base de datos). Suelen ser números o GUID, y se consideran artificiales porque, por sí solos, no hay nada que relacione su valor con la información que identifican de manera única. Un recibo de venta siempre está numerado, porque no tiene nada de natural y también es para auditar: las brechas en los números de recibo generan sospechas. Para demostrar cuán arbitraria es la numeración, aquí está la tabla de estados de EE. UU. Pero utilizando un número entero para la columna de la clave principal, US_STATE_CODE:

 US_STATE_PRIMARY_KEY US_STATE -------------------------- 100 Alabama 101 Alaska 102 Arizona 103 Arkansas 104 California 

No hay ningún requisito para comenzar el valor en uno; algunas tiendas lo usan como una medida de seguridad para frustrar la inyección de SQL. El valor es secuencial en función del orden alfabético del nombre del Estado, pero no se puede garantizar. Pero a diferencia de la clave natural, si el nombre de estado cambió, solo se debe actualizar una columna.

Columna única frente a compuesto

Idealmente, una columna será la clave principal, pero tome la decisión en función de los datos disponibles: no combine columnas solo por el hecho de tener una sola columna. Si utiliza los datos del calzador, use un carácter para separar los datos fácilmente (aunque las operaciones para hacer esto no podrán aprovechar un índice si está presente).

Actuación

Desde una perspectiva de rendimiento, los enteros son mejores porque ofrecen un rango decente de valores y el número de bytes utilizados es pequeño cuando se compara con VARCHAR de cinco o más caracteres.

El diseño de la base de datos comienza con un modelo de datos conceptual (como un diagtwig de relaciones entre entidades) y finaliza con un esquema de base de datos o esquemas. Las entidades se asignan a tablas; en este proceso, una entidad se puede dividir en varias tablas, varias entidades se pueden fusionar en una sola tabla y pueden surgir nuevas tablas (por ejemplo, tablas de intersección para implementar relaciones de muchos a muchos).

En un ERD, las entidades tienen claves primarias. Estas son claves naturales, es decir, son atributos de la entidad. Para una entidad PERSONA podría ser SocialSecurityNumber. Para una entidad ORDER, si podría ser OrderRef Para una entidad FACTURA, podría ser Factura No. En el primer caso, es un identificador de la vida real; en el segundo caso, es una llave inteligente en un formato feo (2010 / DEF / 000023); en el tercer caso, es un número monotónicamente creciente porque eso es lo que usa el sistema basado en papel actual.

Las llaves naturales pueden ser extravagantes. Una vez trabajé en un diseño de base de datos donde el analista había especificado la entidad CLIENTE con una clave de (Nombre completo, Dirección, Sexo, Fecha de nacimiento, Características distintivas) sobre la base de que dos personas del mismo nombre, fecha de nacimiento y sexo podían vivir al mismo dirección.

Las características de la clave principal de una entidad son:

  • único
  • familiar
  • estable (presumido)
  • mínimo (uno o más atributos pero tan pocos como sea necesario)

Cuando se trata de claves primarias para tablas de bases de datos, las claves naturales no siempre son adecuadas.

Hay muchas razones para no usar el SSN como clave primaria física. La protección de los datos personales de un ciudadano es en realidad la más importante, pero también es cierto que el número de un individuo puede cambiar. Las claves primarias deben ser invariables.

Las llaves inteligentes son tontas. En realidad, son claves compuestas comprimidas en una sola columna. Están mejor representados como columnas separadas, sobre todo porque es un requisito frecuente buscar elementos individuales de la clave. Además, el formato de tales claves puede cambiar.

En general, las claves compuestas son un dolor como claves primarias porque tenemos que poner en cascada varias columnas como claves foráneas. Esto se agrava cuando la clave principal del niño se define como un número de serie dentro de la clave principal del padre. Existen sistemas por los cuales las tablas dependientes heredan una clave externa de nueve columnas de un elemento primario cuando tienen dos columnas de datos separadas. A veces, este tipo de herencia puede ser útil, pero sobre todo es simplemente una molestia.

Las características de la clave principal de una entidad son:

  • único
  • apropiado (sin sentido)
  • estabilidad garantizada
  • mínimo, por lo general una sola columna (a excepción de las tablas de intersección)

Entonces, a menos que la clave candidata sea un identificador sin sentido (como InvoiceNo) una tabla debe tener una clave sintética (AKA clave sustituta). Esto puede ser un número incremental monótonamente o un GUID según sus necesidades. En cuanto a las tablas de intersección, si no tienen otros atributos o tablas dependientes, no hay ningún valor en la sustitución de una clave primaria compuesta (AKA compuesta clave) por una sintética.

Lo crucial es que aún aplicamos las claves candidatas . Esto significa aplicar restricciones ÚNICAS a esas columnas – SSN, OrderRef – en la tabla padre. Esto se debe a que una clave sintética identifica de manera única una fila en una tabla, no identifica de forma exclusiva los datos.


En cuanto a la familiaridad

La familiaridad es rizada. Es una consideración importante cuando se trata de identificar claves primarias en un modelo conceptual de datos, pero es menos útil cuando se trata de diseño de bases de datos.

En una conversación, @bbadour proporciona dos ejemplos contrastantes:

 {3296013,840082470,Bob Badour,745} versus {840082470,Bob Badour,PE,CA} 

y plantea la pregunta:

“Lo que logra 3296013, no fue alcanzado por 840082470, que es la clave principal para mis registros académicos en cualquier escuela postsecundaria en Canadá”.

Bueno, 840082470 es como un número de factura. De por sí es una cadena de dígitos sin sentido. Si el sistema que estamos diseñando pertenece al dominio de la educación superior canadiense, entonces es ciertamente aceptable como una clave candidata. Sin embargo, debido a que es una clave aparentemente propiedad de un sistema central externo (perdóneme por no entender el sistema académico canadiense), está abierto a algunas de las objeciones al SSN como clave principal. Dependemos de ese sistema externo para garantizar la exclusividad, garantizar la estabilidad y verificar la identificación.

En cuanto a 745 versus PE, CA , eso está claramente mal. La abreviatura postal canadiense para “Prince Edward Island” y el dígrafo ISO para “Canadá” identifican dos piezas distintas de información y derivan de diferentes fonts, por lo que deben representarse como dos columnas separadas. Pero centrémonos en si 745 o PE son la mejor clave primaria.

En primer lugar, a la base de datos no le importa qué tipo de datos usamos para que el código represente “Prince Edward Island”. Solo quiere una unicidad garantizada.

En segundo lugar, es probable que la parte del sistema orientada al usuario muestre la expansión completa “Prince Edward Island”, en cuyo caso la aplicación deberá ejecutar una búsqueda de todos modos. Esto se debe a que los usuarios de un sistema que también posee direcciones del país de Perú o del estado de California apreciarán la claridad de los nombres ampliados [1]. Ciertamente, si vamos más allá de los pocos casos difíciles (como las abreviaturas de estado), la aplicación siempre debe expandir los códigos cuando se muestran a los usuarios.

Por lo tanto, la única ventaja de usar PE en lugar de 745 es que facilita las consultas ad hoc.

En tercer lugar, si la expansión del código cambia, es posible que deseemos distinguir los registros que usan la versión más nueva. Esto es mucho más fácil si 745='Prince Edward Island' y 746='Prince Edward Is.' que si usamos PE como la clave principal.

En cuarto lugar, hay consideraciones de progtwigción. Por ejemplo, si los desarrolladores de aplicaciones tienen que proporcionar listas desplegables usando Enumeraciones Java, necesitan códigos numéricos.

En resumen, la familiaridad de las claves naturales no es tan útil como la practicidad de las claves sustitutivas.

[1] Los canadienses sabrán que CA significa Canadá. Pero, ¿MO significa Marruecos, Mónaco, Moldavia, Montenegro, Mongolia o Montserrat? En realidad ninguno de ellos: es Macau.

Una clave candidata es un conjunto de atributos que son irreductiblemente únicos (es decir, un significado irreductible que no permite eliminar ningún atributo de la clave sin perder la propiedad de unicidad).

Otros criterios al elegir qué claves candidatas implementar son: simplicidad, estabilidad, familiaridad.

Estos tres criterios son consideraciones importantes pero no necesariamente atributos esenciales de una clave. Por ejemplo, puede ser deseable y bastante razonable aplicar una clave que puede cambiar a menudo. por ejemplo: se requiere que el nombre de usuario sea único, pero el usuario puede cambiarlo a voluntad siempre que siga siendo único.

Una clave principal es una clave candidata.

Una clave principal es una clave que identifica de forma única a una entidad. Cuando elige una clave principal, la mejor opción es casi siempre una clave sustituta que no tiene absolutamente nada que ver con la entidad que no sea identificarla de manera única.

Y eso es. Hay supuestos casos extremos donde una clave principal puede ser una clave natural, pero nunca he visto una válida.

La mayoría de nosotros usamos un entero de incremento automático de 32 bits como clave principal. Otra opción excelente (en ciertas circunstancias) es un UUID.

Oye. está abierto de nuevo. Aquí va.

(1) Elija buenas claves candidatas.

No pertenece al diseñador de la base de datos para elegir las claves candidatas. El diseñador de la base de datos tiene la responsabilidad de velar por que se cumplan todos los requisitos de exclusividad que el usuario le informe. Por lo tanto, es el usuario quien “elige” cuáles son las claves candidatas.

Hay dos situaciones en las que puedo pensar que relajan un poco esta posición inequívoca.

Una es si el usuario dice que algún atributo de tipo ‘video’ o ‘audio’ (o algo así) debe ser único. Puede que no sea factible aplicarlo realmente, y es responsabilidad del diseñador señalarlo al usuario (ya que también es su responsabilidad señalar que la “singularidad” del contenido de audio y video es un tema muy discutible, y que cualquier la singularidad en dichos valores de atributo, incluso si el sistema lo aplica, todavía tiene buenas posibilidades de no ser la misma singularidad que el usuario desea).

En segundo lugar, la manera en que la imagen se enturbia por la posibilidad de diseños lógicos distintos que aborden el mismo problema. Si D1 y D2 son ambos diseños válidos que abordan el mismo problema, entonces podría darse el caso de que una determinada regla de unicidad dada impuesta por el usuario sea aplicable usando claves en D1, pero no en D2. Desde esta perspectiva, “elegir las claves candidatas” se puede interpretar como “elegir un diseño particular de modo que una regla de singularidad dada sea aplicable mediante claves”. Pero esa no fue realmente la pregunta que usted hizo.

(2) Elija buenas claves primarias.

Hace un tiempo, Darwen lanzó la pregunta “¿Cuáles son buenas razones para seleccionar a un candidato en particular de entre los demás como ‘primario’?”. No salió mucho, excepto entonces quizás: “sugerir que esta clave en particular es la preferida para usar siempre que se haga referencia a esta relvar”. Sospecho que no encontraron lo suficientemente convincente como para cambiar su decisión anterior de que “ninguna clave es más única que cualquier otra”.

Pero, suponiendo que exista alguna razón válida para seleccionar una clave en particular como “primaria”, supongo que se aplican las siguientes consideraciones:

  • la verosimilitud o conveniencia de usar esta clave principal también como, por ejemplo, la clave de agrupamiento en el diseño físico.
  • y como consecuencia de eso, la probabilidad de tener que cambiar un valor de alguna clave primaria existente. Los valores clave que son altamente estables serán preferibles a los valores clave que son más volátiles.
  • el porcentaje del negocio que naturalmente usa alguna de esas claves en sus operaciones diarias.
  • si el espacio requerido para la encoding física de valores clave es significativamente diferente, cuál tiene el tamaño de encoding más pequeño.

Su respuesta a Erwin: “Estoy de acuerdo en que elegir una clave primaria simplemente designa una clave candidata como preferida para las referencias de clave externa. Sin embargo, incluso si eliminamos por completo el nombre” clave primaria “, los diseñadores aún deben elegir qué clave candidata propagar en otra relación con fines de referencia. Si los usuarios identifican una relación fuertemente referenciada con una clave compuesta inestable, ¿quiere sugerir que el diseñador no tiene ningún negocio eligiendo una clave simple, estable adicional? ¿O usando la clave simple y estable para hacer referencia a la relación? Su sección de clave candidata parece implicar eso. – bbadour hace 8 horas ”

Su pregunta original fue sobre ‘claves principales’. Ahora cambia su enfoque a teclas y claves externas. Una clave es una restricción de integridad, por lo que el único criterio es que un conjunto mínimo de atributos debe ser único en una relación (unicidad e irreductibilidad). Si cambiamos nuestro enfoque a las claves externas, la simplicidad, la estabilidad y la familiaridad son los criterios para elegir entre todas las claves candidatas en la relación de referencia. Podría haber más claves candidatas que cumplan esos criterios en mayor o menor medida. Si observamos la familiaridad, una clave candidata podría ser muy familiar para un grupo de usuarios y no para otro grupo para el cual otra clave candidata es más familiar. Piense en diferentes vistas o subesquemas de una base de datos. Este segundo grupo de usuarios debe elegir una clave candidata diferente para fines de referencia (como clave externa). Si insiste en ‘claves primarias’ de las cuales solo tenemos una por relación, entonces tengo que preguntar qué hace que una clave sea más primaria que otras. Creo que el término clave principal no debe usarse. Al menos en el nivel lógico. Además, el término ‘claves externas’ no está bien elegido (las claves externas no son claves, sino referencias).

Por lo tanto, creo que las observaciones de Erwin sobre las claves “primarias” fueron muy útiles. O al menos esta fue mi interpretación de lo que él quiere decir.

¿Estás de acuerdo con esto? De ser así, ¿cambiaría su pregunta original a “¿Cuáles son los criterios de diseño para las claves y cuáles son los criterios para elegir una clave externa de las claves candidatas disponibles?”? Si no, ¿por qué?

Saludos, Carlos

Una clave principal es una clave candidata elegida para un tratamiento especial, por lo que primero debemos ver las propiedades de las claves candidatas. Un conjunto de una o más columnas es una clave candidata si tiene las dos propiedades siguientes:

Singularidad: una clave candidata debe identificar de forma única cada fila en una tabla. Ninguna tabla puede contener dos filas con el mismo valor para la clave candidata.

Irreducabilidad: eliminar cualquier columna de una clave candidata debe violar la propiedad uniqness. En otras palabras, ningún subconjunto de columnas en una clave candidata es en sí mismo una clave candidata.

Si no existe una clave candidata, y, a veces, incluso si lo hace, a menudo se crea una clave sustituta utilizando una columna entera autoincrementada, o se compone de alguna otra técnica. Esta clave sustituta es ahora también una clave candidata.

A menudo es útil elegir entre las claves candidatas disponibles y designar una de ellas como clave principal. El primer criterio que se aplica con frecuencia es la simplicidad que indica la clave candidata con la menor cantidad de columnas. Sin embargo, hay otros criterios potenciales, como la familiaridad, los valores familiares son más útiles que los valores no familiares, y la estabilidad, las claves estables son menos problemáticas que las claves que pueden cambiar. Sin embargo, estos criterios están estrictamente fuera del scope del modelo relacional, a menudo entran en conflicto entre ellos y, a menudo, se hacen para abordar las limitaciones de la implementación.

Diría que los dos primeros conceptos “unicidad” e “irreducabilidad” son menos criterios de diseño que propiedades fundamentales de claves primarias, mientras que los últimos conceptos de “simplicidad”, “familiaridad” y “estabilidad” son criterios de diseño mejor etiquetados, como implican concesiones y subjetividad.

¿Por qué elegir una clave principal? La simplicidad y la familiaridad no son solo criterios para elegir entre las claves candididate disponibles, sino que son la razón por la que debemos elegir una clave primaria. Si hay varias claves candidatas en una tabla, simplifica las cosas si todas las claves externas que apuntan a esa tabla hacen referencia a la misma clave candidata. Además, el solo hecho de elegir una clave candidata en particular lo ayudará a familiarizarse.

¿Cuáles son los criterios?

UNA PRIMARY KEY es algo que definirá la entidad, solo la entidad y nada más que la entidad.

  • Puedes tomarlo del mundo exterior. Digamos, un número de catálogo de estrella para identificar una estrella (buen ejemplo), o un SSN para identificar a una persona (mal ejemplo).

    En este caso, confías en el mundo exterior.

    • ¿Todas las personas tienen SSN ? (No lo hacen).
    • ¿Son los SSN únicos? (No lo son)
    • ¿Se puede asignar un SSN a otra persona? (Puede).
  • Puede generarlo dentro de su modelo, usando AUTOINCREMENT o AUTOINCREMENT o lo que sea.

    En este caso, confía en usted y sus habilidades de base de datos.

    • ¿Todas las personas en su modelo tienen una ID ? (Sí, lo hacen, de lo contrario no estarían en la tabla con ID NOT NULL ).
    • ¿Estas ID's únicas? (Sí, lo son, la restricción PRIMARY KEY se encarga de ello).
    • ¿Pueden ser asignados a otras personas? (No, no pueden, son no repetibles por diseño o incremento automático).

    O otro conjunto de respuestas:

    • ¿Todas las personas en su modelo tienen una ID ? (No, no lo hacen, la tabla de personas se eliminó accidentalmente, aunque se retuvo otra información).
    • ¿Estas ID's únicas? (No, fallamos al fusionar dos versiones de la base de datos correctamente).
    • ¿Pueden ser asignados a otras personas? (Sí, reiniciamos AUTOINCREMENT por error).

Lo más importante es que una clave sustituta es una fiesta que siempre está contigo. Siempre puede crear una clave sustituta: nada en la Tierra puede evitar que declare un campo AUTOINCREMENT . Pero, por mucho, no todas las cosas tienen algún tipo de identificador con el que todos estén de acuerdo.

Sin embargo, una buena clave natural no puede ser exagerada.

Guide Star Catalog base de datos de Guide Star Catalog probablemente esté respaldada de manera más confiable que la suya, y la lista de códigos de estado de US Que siempre puede restaurar directamente desde la memoria.

(No estoy muy seguro de cómo interpretar esta pregunta. Suena como una prueba o algo en lo que busca una sola respuesta “correcta” de un libro de texto. Voy a interpretar la pregunta como una más práctica, de ahí mi consejo a continuación. )

Al menos en el mundo de MS SQL, la discusión sobre una Clave principal adecuada está inevitablemente envuelta en una discusión sobre el índice agrupado apropiado para una tabla. Los dos no tienen que ser iguales, pero están por defecto, y para muchas tablas, hacer que los dos sean lo mismo a menudo es una buena idea.

Para el propósito de nuestra discusión aquí, es importante distinguir entre los dos:

UNA CLAVE PRINCIPAL es un campo o combinación de campos que identifican una fila de manera única.
Un ÍNDICE CLUSTER es un campo o combinación de campos que representa el orden físico de una tabla. (De nuevo, estoy hablando de MS SQL Server, no estoy seguro de cómo otros RDBS podrían manejar esto)

La clave para el rest de mi discusión es saber que desde SQL 7.0, la clave del índice agrupado se usa como un identificador de fila para todos los índices no agrupados . Esto significa que muchos de los mismos criterios para elegir una buena clave de agrupamiento son los mismos que para elegir una buena clave principal.

Primero veamos los criterios para un buen índice agrupado ( del excelente artículo de Kimberly Tripp ). Un índice agrupado debería ser:

  1. Único : inútil como identificador de fila para otros índices
  2. Estrecho : esta clave se usa en otros índices, por lo que debe ser lo más estrecha posible
  3. Estático : si los valores clave cambian, las referencias dejan de ser válidas y es necesario actualizarlas
  4. En constante aumento : para reducir la fragmentación de la tabla física a medida que se agregan nuevas filas

Es evidente que los primeros 3 son también buenos criterios para una clave primaria. # 4 es una bonificación que reducirá la fragmentación de la tabla a medida que crecen las tablas.

Un GUID como clave principal, tan popular como es, en realidad falla 2 de estos criterios (Estrecho y siempre creciente). Como tal, no se recomienda como un PK / índice agrupado en la mayoría de las circunstancias (ver el artículo relacionado de Kim aquí )

Solo uno realmente, elija un suplente para cada tabla (identity / auto_number) o algo similar que los usuarios nunca verán para que pueda hacer lo que sea necesario con ellos cuando lo necesite ahora y en el futuro.

Voy a decir algo aquí que no se espera.

Todo lo que enseñan en la base de datos sobre normalización y claves es incorrecto cuando se trata de elegir claves primarias.

La clave principal es especial cuando se trata de consultas de rango, y por esa razón si tiene una consulta de rango dominante que es su clave principal, no hay excepciones.

Si su consulta de rango dominante no está en una clave candidata, ¡terminará con una clave principal que no se aplica para la exclusividad! Esto a veces se llama un índice agrupado, que es un nombre inapropiado porque no hay índice.

Ahora las claves de normalización y candidato son todas importantes, y querrás imponer restricciones únicas en al menos algunas de ellas. Pero no asigne la clave principal porque es la clave natural. De hecho, esto es más lento que definir un índice y una restricción única. Defina la clave principal según las consultas de rango solamente.

Recuerde, no hay restricción para tener claves principales. Una tabla sin claves principales se denomina tabla de acumulación y no tiene un orden intrínseco o una orden intrínseca de inserción.

EDITAR: definición de consulta de rango:
Una consulta de rango es una consulta que es una consulta ORDER BY o contiene un operador mayor o menor que. Lo que nos interesa son las columnas en las que se ejecutan estas consultas. La idea fundamental es una consulta de rango obtiene varias filas (de decenas a cientos, quizás miles pero no todas) de la tabla basadas en condiciones de límite en uno o ambos extremos.
Hay otro tipo de consultas de rango, y es allí donde tiene una clave externa para otra tabla y una operación selecciona todas las coincidencias en esa clave externa. De hecho, esto también es una consulta de rango, aunque no obviamente así.