algorithm - examples - relational database model

¿Cómo te gustan tus llaves principales? (26)

Debería haber al menos 3 columnas que conforman la clave principal.

No entiendo esto.

¿Estás hablando de una "clave natural", por ejemplo, "nombre y fecha de nacimiento"? Una clave natural puede ser ideal si existe, pero la mayoría de los candidatos para una clave natural no son únicos (varias personas con el mismo nombre) o no son constantes (alguien puede cambiar su nombre).

Int / BigInt que autoincremento son claves primarias suficientemente buenas.

Prefiero a Guid. Un problema potencial con la autoincrementación es que el valor (por ejemplo, "ID de pedido") es asignado por la instancia de la base de datos (por ejemplo, por la "base de datos de ventas") ... lo cual no funcionará del todo (en cambio, comienzas a necesitar claves compuestas) alguna vez necesita fusionar datos creados por más de una instancia de base de datos (por ejemplo, desde varias oficinas de ventas, cada una con su propia base de datos).

En una discusión bastante animada en mi equipo, me hicieron pensar lo que a la mayoría de las personas les gusta como claves principales. Tuvimos los siguientes grupos-

Int / BigInt que autoincremento son claves primarias suficientemente buenas.
Debe haber al menos 3 columnas que componen la clave principal.
Id, GUID y identificadores de filas legibles para personas, todos deben tratarse de manera diferente.

¿Cuál es el mejor enfoque para PKs? Sería increíble si pudieras justificar tu opinión. ¿Hay un mejor enfoque que el anterior?

EDITAR: ¿Alguien tiene una muestra simple / algoritmo para generar identificadores legibles para las filas que se escala bien?

Aumento automático de columnas. Puedo hacer que mi código funcione a la perfección con SQL Server u Oracle, uno usa la identidad y el otro usa secuencias a través de mi DAL, y no podría estar más feliz. Acepto, a veces los GUID son necesarios si está realizando la replicación o enviando datos para recibirlos más adelante en un procesamiento posterior.

Casi siempre enteros.

Tienen otras buenas razones además de ser más pequeño / más rápido de procesar. ¿Qué prefieres escribir - "404040" o "3463b5a2-a02b-4fd4-aa0f-1d3c0450026c"?

Creo que el uso de la palabra "Primario", en la frase "Principal" es en un sentido real, engañoso.

Primero, use la definición de que una "clave" es un atributo o conjunto de atributos que deben ser únicos dentro de la tabla,

Entonces, tener una clave sirve varios propósitos a menudo mutuamente inconsistentes.

Para usar como condiciones de unión a uno o varios registros en tablas secundarias que tienen una relación con esta tabla principal. (Definición explícita o implícita de una clave externa en esas tablas secundarias)
(relacionado) Asegurar que los registros secundarios deben tener un registro padre en la pestaña padre; e (La tabla secundaria FK debe existir como clave en la tabla padre)
Para aumentar la cantidad de consultas que necesitan ubicar rápidamente un registro o una fila específica en la tabla.
Para garantizar la coherencia de los datos evitando que las filas duplicadas que representan la misma entidad lógica se inserten, no la tabla. (Esto a menudo se denomina clave "natural" y debe consistir en atributos de tabla (entidad) que son relativamente invariables).

Claramente, cualquier clave no significativa, no natural (como un GUID o un entero generado automáticamente es totalmente incapaz de satisfacer el # 4.

Pero a menudo, con muchas (la mayoría de las tablas), una clave totalmente natural que puede proporcionar el n. ° 4 a menudo constará de múltiples atributos y será excesivamente amplia, o tan amplia que usarla con los propósitos n. ° 1, n. ° 2 o n. ° 3 causará inaceptables consecuencias del rendimiento.

La respuesta es simple. Usa ambos. Use una clave integral de autogeneración simple para todas las combinaciones y FK en otras tablas secundarias, pero asegúrese de que cada tabla que requiera coherencia de datos (pocas tablas no lo tengan) tenga una clave única natural alternativa que evitará insertos de filas de datos incoherentes. Además, si siempre tienes ambas, entonces todas las objeciones contra el uso de una clave natural (¿qué pasa si cambia? Tengo que cambiar cada lugar al que se hace referencia como FK) se vuelven discutibles, ya que no lo estás usando para eso. .. Solo lo estás usando en una tabla donde es un PK, para evitar datos duplicados inconsistentes ...

En cuanto a los GUID, tenga mucho cuidado al usarlos, ya que usar guids en un índice puede manchar la fragmentación del índice. Los algoritmos más comunes utilizados para crearlas colocan la porción "aleatoria" del guid en las posiciones más significativas de los bits ... Esto aumenta el requisito para la desfragmentación del índice regular / Reindexing a medida que se agregan nuevas filas.

En general, depende.

Personalmente, me gusta el autoincrement ints.

Pero, una cosa que puedo decir es que nunca confíes en los datos de otras fuentes como tu clave. Te lo juro, cada vez que lo hago, vuelve a morderme. Bueno, nunca más!

Este es solo un tema religioso porque las personas buscan una respuesta correcta universal. El hecho de que tanto tu equipo como este hilo SO muestren tanto desacuerdo debería ser una pista de que hay buenas razones para usar todas las soluciones que describes, en diferentes circunstancias.

Las claves sustitutas son útiles cuando ningún otro atributo o conjunto de atributos en la tabla es adecuado para identificar las filas de manera única.
Se prefieren las teclas naturales, cuando sea posible, para hacer que la tabla sea más legible para los humanos. Las claves naturales también permiten que la clave externa en una tabla dependiente contenga un valor real en lugar de una identificación sustituta. Por ejemplo, cuando necesite almacenar el state (CA, TX, NY), también podría utilizar una clave natural char(2) en lugar de una int.
Use claves primarias compuestas donde sea apropiado. No agregue una clave sustituta " id " innecesariamente cuando exista una clave compuesta perfectamente buena (esto es especialmente cierto en tablas de muchos a muchos). Un mandato para una clave de tres columnas en cada tabla es una absoluta tontería.
Los GUID son una solución cuando necesita preservar la singularidad en múltiples sitios. También son útiles si necesita valores en la clave principal para ser únicos, pero no ordenados o consecutivos.
INT vs. BIGINT: no es común que una tabla requiera un rango de 64 bits para las claves primarias, pero con la disponibilidad cada vez mayor de hardware de 64 bits no debería ser una carga, y ofrece más garantías de que no se desbordará. INT es, por supuesto, más pequeño, por lo que si el espacio es escaso puede dar una ligera ventaja.

Este es un clásico "depende". No hay una respuesta correcta para cada proyecto. Me gustan las cosas diferentes para diferentes situaciones. Depende de si estoy usando un ORM y qué es lo que admite. Depende de la arquitectura general (distribuida o no, etc.). Solo elija uno que crea que funcionará y pase a discutir sobre pestañas y espacios.

GUIAS de RE

Tenga cuidado con si realmente va a ser una base de datos REALMENTE REALMENTE REALMENTE GRANDE, con mucha carga y acceso rápido.

En mi último trabajo, donde teníamos bases de datos de entre 100 y 500 millones de registros, nuestros muchachos de la base de datos argumentaron fuertemente en contra de los GUID y de un número decimal del tamaño adecuado. Sintieron que (bajo Oracle) la diferencia de tamaño en el almacenamiento interno para una cadena Guid - vs- un valor decimal haría una diferencia muy notable en las búsquedas. (Teclas más grandes = árboles más profundos para atravesar)

La naturaleza aleatoria de los GUID también reduce significativamente el factor de relleno para las páginas de índice, lo que aumenta drásticamente el desgarro y la E / S del disco.

La forma en que abordo las teclas principales (y creo que es la mejor) es evitar tener un enfoque "predeterminado". Esto significa que en lugar de simplemente presionar un entero autoincrementado y llamarlo un día miro el problema y digo "¿hay alguna columna o grupo de columnas que siempre estarán separadas y no cambiarán?" Si la respuesta es sí, entonces tomo ese enfoque.

Más allá de una respuesta de definición básica, lo que constituye una buena clave primaria se deja en gran parte a la religión y rompe los argumentos de la sala. Si tiene algo que es, y siempre lo hará, un mapa exclusivo de una fila individual, entonces funcionará bien como clave principal. Pasado ese punto, hay otras consideraciones:

¿La definición de la clave principal no es demasiado compleja? ¿Evita introducir complejidad innecesaria por el bien de seguir una "mejor práctica"?
¿Existe una mejor clave primaria posible que requiera menos sobrecarga para que la maneje la base de datos (es decir, INTEGER vs. VARCHAR, etc.)?
¿Estoy ABSOLUTAMENTE seguro de que la unicidad e invariante de definición de mi clave principal no cambiará?

Este último es probablemente lo que atrae a la mayoría de la gente a usar cosas como GUID o columnas de números enteros que se incrementan a sí mismas, ya que al depender de cosas como direcciones, números de teléfono, nombre / apellido, etc., simplemente no se corta. El único invariante sobre las personas en las que puedo pensar es en los números de seguro social, pero entonces no estoy ni siquiera 100% seguro de que los que quedan sean únicos para siempre.

Espero que esto ayude a agregar algo de claridad ...

Me gusta el blog The Database Programmer como fuente de este tipo de información.

3 columnas para una clave principal? Yo diría que las columnas deberían tener restricciones únicas apropiadas según lo exijan las reglas comerciales, pero aún tendría una clave sustituta por separado. Las claves compuestas significan que la lógica de negocios entra en la clave. Si la lógica cambia, todo su esquema está atornillado.

Me gusta el mío único.

Me gustan las llaves naturales, siempre que puedo confiar en ellas. Estoy dispuesto a pagar un pequeño precio de rendimiento para poder usar claves que tengan sentido para los expertos en la materia.

Para las tablas que describen entidades, debe haber una clave natural simple que identifica las instancias individuales de la misma manera que las personas en cuestión. Si el tema no tiene identificadores confiables para una de las entidades, recurriré a una clave sustituta.

Para las tablas que describen relaciones, uso una clave compuesta, donde cada componente hace referencia a una entidad que participa en la relación y, por lo tanto, una fila en una tabla de entidades. Una vez más, el rendimiento alcanzado por usar una clave compuesta es generalmente mínimo.

As others have pointed out, the term "primary key" is a little misleading. In the Relational Data Model, the term that''s used is "candidate keys". There could be several candidate keys for a single table. Logically, each one is just as good as another. Choosing one of them as "primary" and making all references via that key is simply a choice the designer can make.

No veo una respuesta que señale (lo que considero como) el punto realmente fundamental, a saber, que una clave primaria es lo que garantiza que no se obtendrán dos entradas en la tabla para la misma entidad del mundo real (como modelado en la base de datos). Esta observación ayuda a establecer qué son buenas y cuáles son las malas elecciones para la clave principal.

Por ejemplo, en una tabla de nombres y códigos de estado (EE. UU.), El nombre o el código podría ser la clave principal: constituyen dos claves candidatas diferentes, y se elige uno de ellos (normalmente el más corto, el código) como Clave primaria. En la teoría de las dependencias funcionales (y las dependencias de unión, de 1NF a 5NF), las claves candidatas son cruciales en lugar de una clave principal.

Para un contraejemplo, los nombres humanos generalmente hacen una mala elección para la clave primaria. Hay muchas personas que reciben el nombre de "John Smith" u otros nombres similares; incluso teniendo en cuenta los nombres intermedios (recuerde: no todos tienen uno, por ejemplo, yo no), hay mucho margen para la duplicación. En consecuencia, las personas no usan nombres como claves principales. Inventan claves artificiales como el Número de Seguridad Social (SSN) o el Número de empleado y los utilizan para designar al individuo.

Una clave principal ideal es corta, única, memorable y natural. De estas características, la singularidad es obligatoria; el resto tiene que flexionarse dadas las limitaciones de los datos del mundo real.

Cuando se trata de determinar la clave primaria de una tabla dada, por lo tanto, debe observar lo que esa tabla representa. ¿Qué conjunto o conjuntos de valores de columna en la tabla identifica de manera única cada fila en la tabla? Esas son las claves candidatas. Ahora, si cada clave candidata consta de 4 o 5 columnas, entonces puede decidir que esas son demasiado torpes para hacer una buena clave primaria (principalmente por motivos de falta de precisión). En esas circunstancias, puede introducir una clave sustituta, un número generado artificialmente. Muy a menudo (pero no siempre) un número entero simple de 32 bits es suficiente para la clave sustituta. A continuación, designa esta clave sustituta como la clave principal.

Sin embargo, debe asegurarse de que las otras claves candidatas (para que la clave sustituta sea una clave candidata también, así como la clave principal elegida) se mantienen como un único identificador, normalmente al establecer una restricción única en esos conjuntos de columnas.

A veces, a las personas les resulta difícil identificar qué hace que una fila sea única, pero debería haber algo para hacer eso, porque simplemente repetir una información no lo hace más cierto. Y si no tiene cuidado y obtiene dos (o más) filas que pretenden almacenar la misma información, y luego necesita actualizar la información, existe el peligro (especialmente si usa los cursores) de que solo actualice una fila en lugar de cada fila, por lo que las filas están fuera de sincronía y nadie sabe qué fila contiene la información correcta.

Esta es una vista bastante dura, en algunos aspectos.

No tengo ningún problema particular con el uso de un GUID cuando se necesitan, pero tienden a ser grandes (como en 16-64 bytes) y se usan con demasiada frecuencia. Muy a menudo un valor de 4 bytes perfectamente bueno sería suficiente. Usar un GUID donde un valor de 4 bytes sería suficiente desperdicia espacio en el disco, y ralentiza incluso el acceso indexado a los datos ya que hay menos valores por página de índice, por lo que el índice será más profundo y se tendrán que leer más páginas para acceder al información.

Oh, una razón más por la que uso GUIDs: utilizo una estructura de datos jerárquica. Es decir, tengo una tabla ''Empresa'' y una tabla ''Vendedor'' para las que coinciden las Teclas principales. Pero también tengo una tabla ''Fabricante'' que también ''hereda'' de la Compañía. Los campos que son comunes a Vendedores y Fabricantes no aparecen en esas tablas, aparecen en la Compañía. En esta configuración, usar int es mucho más doloroso que Guids. Por lo menos, no puede usar claves principales de identidad.

Si vas a hacer una sincronización entre bases de datos con aplicaciones conectadas ocasionalmente, entonces deberías estar usando GUID para tus claves principales. Es un poco doloroso para la depuración, así que, aparte de ese caso, tiendo a apegarme a los ints que se autoincrementan.

Los aumentos automáticos deben ser los predeterminados, y no usarlos debe estar justificado.

Siempre he usado una clave sustituta, un entero autoincrementing llamado ''id''. Puedo ver muchas razones para hacer esto incluso cuando otra opción es obvia:

Consistencia
Datos independientes (únicos, no destruidos por cambios en el formato)
Humano-legible

... y sin una razón sensata para no:

¿Ambigüedad en las uniones? - Alias tablas es una mejor práctica, en mi humilde opinión
Tablas óptimas? - Eliminar un byte por entrada es una optimización prematura, en mi humilde opinión
Decisión por mesa? - Ya no es consistente
¿Problemas de escala? - ¿Eh? ¿Por qué?
Estructura de datos jerárquica? - Eso es denormalización, un tema totalmente diferente de la religión. Baste decir que soy fan en algunas circunstancias en teoría, pero nunca en la práctica :)

razones sensatas en contra de lo que no he pensado o encontrado aún son bienvenidas ...

Siempre uso la clave sustituta. Una clave sustituta (generalmente una columna de identidad, autoincrement o GUID) es aquella en la que la clave no está presente en los datos en sí. Una clave natural, por otro lado, es una que, por sí sola, identifica de manera única la fila. Por lo que puedo decir en la vida, casi no hay claves naturales reales . Ni siquiera las cosas como SSN en los Estados Unidos son una clave natural. Las claves primarias compuestas son un desastre que espera suceder. No puede editar ninguno de esos datos (que es el mayor inconveniente de cualquier clave natural, compuesta o no), pero lo peor es que con una clave compuesta, ahora debe perpetuar esos datos clave en cada tabla relacionada. Qué desperdicio gigante.

Ahora, para la selección de la clave sustituta, me quedo con columnas de identidad (trabajo principalmente en MS SQL Server). Los GUID son demasiado grandes y Microsoft recomienda no usarlos como PK. Si tiene varios servidores, todo lo que necesita hacer es realizar el incremento 10 o 20 o lo que sea que crea la cantidad máxima de servidores que necesitará sincronizar / expandir, y solo agregar la semilla para cada tabla en cada servidor subsiguiente , y nunca tendrás una colisión de datos.

Por supuesto, debido al incremento, hago que la columna de identidad sea BigInt (también conocida como larga [64 bits]).

Haciendo un poco de matemática, incluso si haces el incremento de 100, todavía puedes tener 92,233,720,368,547,758 (> 92 cuatrillones) filas en tu mesa.

Solo un poco relevante, pero una cosa que he empezado a hacer recientemente cuando tengo pequeñas tablas de clasificación (esencialmente aquellas que representarían ENUM en el código) es que convertiré la clave principal en una char (3) o char (4). Luego hago que esas claves principales sean representativas del valor de búsqueda.

Por ejemplo, tengo un sistema de cotización para nuestros Agentes de ventas internos. Tenemos "Categorías de Costos" que cada línea presupuestaria se asigna a uno de ... Así que tengo una tabla de búsqueda de tipo llamada ''tCostCategories'', donde la clave principal es ''MTL'', ''SVC'', ''TRV'', ''TAX'', ''ODC''. Otras columnas en la tabla de búsqueda almacenan más detalles, como el significado normal en inglés de los códigos, "Material", "Servicio", "Viajes", "Impuestos", "Otros costos directos", etc.

Esto es realmente bueno porque no usa más espacio que un int, y cuando mira los datos de origen, no tiene que vincular la tabla de búsqueda para saber qué valor tiene. Por ejemplo, una fila de cita podría ser similar a:

1 piezaNúmero $ 40 MTL
2 OtherPartNumber $ 29.99 SVC
3 PartNumber2 $ 150 TRV

Es mucho más fácil que usar un int para representar las categorías y luego vincular 1, 2, 3 en todas las líneas; tienes los datos justo frente a ti, y el rendimiento no parece afectado en absoluto (no es que yo '' realmente probado.)

En cuanto a la verdadera pregunta ... me gustan los identificadores únicos de RowGUID. No estoy al 100% en esto, pero ¿no todas las filas tienen RowGuid interno de todos modos? Si es así, usar el RowGuid en realidad tomaría menos espacio que los ints (o cualquier otra cosa). Todo lo que sé es que si es lo suficientemente bueno para que M $ lo use en GreatPlains, entonces es suficiente para mí. (¿Debería agacharme?)

Solo uso un autoincremento int o un GUID. El 99% del tiempo he usado el autoincremento int. Es justo lo que me enseñaron a usar cuando aprendí por primera vez sobre las bases de datos y nunca me encontré con un motivo para no usarlas (aunque conozco las razones por las que un GUID sería mejor).

Me gustan los incrementos automáticos porque ayudan a la legibilidad. Por ejemplo, puedo decir "eche un vistazo al registro 129383" y es bastante fácil para alguien entrar y encontrarlo. Con un GUID que es casi imposible de hacer.

Soy fan del autoincremento como clave principal. Sé en lo profundo de mi corazón que esto es una salida de emergencia, pero hace que sea tan fácil ordenar los datos por cuándo se agregaron (ORDEN POR DESC ID, por ejemplo).

3 columnas suena terriblemente duro para analizar humanamente.

Y ese es el equilibrio: cuánta de la capacidad relacional necesita, en lugar de hacer que ESTE CUADRO SEA CORRECTO, comprensible para un humano que lo interrogue (en comparación con el procedimiento almacenado o la interfaz programática).

auto-incremento es para nosotros los humanos. :-(

Tiendo a usar la opción n. ° 1 o n. ° 3 según el tamaño, la cantidad de personas que se conectan y si se trata de una situación de servidor de base de datos múltiple o no.

La opción n. ° 2 no tiene mucho sentido para mí. Si alguno de los tres no es suficiente para identificar un registro único, entonces es posible (sin pasar por maquinaciones adicionales) que dos tengan dos registros con los mismos valores en las tres columnas. Si desea imponer la exclusividad en cualquier combinación de los tres, simplemente agregue un índice para ellos.

Un poco fuera de tema, pero me siento obligado a responder con ...

Si su clave principal es un GUID, no lo convierta en un índice agrupado . Como los GUID no son secuenciales, los datos se reorganizarán en el disco durante casi cada inserción. (Yuck.) Si usa GUIDs como claves primarias, deberían ser índices no agrupados.

Una cosa que nunca debes hacer es usar una llave inteligente. Esa es una clave donde la información sobre el registro está codificada en la clave misma, y eventualmente morderá.

Trabajé en un lugar, donde la clave principal era la identificación de la cuenta, que era una combinación de letras y números. No recuerdo ningún detalle, pero, por ejemplo, esas cuentas que eran de cierto tipo, estarían en el rango de 600, y de otro tipo, comenzaban con 400. Eso fue genial, hasta que ese cliente decidió pedir ambos tipos de trabajo. O cambió el tipo de trabajo que hicieron.

Otro lugar, usó la ubicación en el árbol como la clave principal para los registros. Entonces habría registros como el siguiente.

Cat1.subcatA.record1 Cat1.subcatA.record2 Cat1.subcatB.record1 Cat2.subcatA.record1

Por supuesto, lo primero que querían los clientes era una forma de mover elementos en el árbol. Todo el conjunto de software murió antes de que eso sucediera.

Por favor, por favor, si está escribiendo código que alguna vez tengo que mantener, ¡no use una llave inteligente!

Guids.period.

In the event that you need to scale out or you need to assign the primary key by alternate means they will be your friend. You can add indexes for everything else.

update to clarify my statement.

I''ve worked on a lot of different kinds of sites. From small single server deals to large ones backed with multiple DB and web servers. There have certainly been apps that would have been just fine with auto incrementing ints as primary keys. However, those don''t fit the model of how I do things.

When using a GUID you can generate the ID anywhere. It could be generated by a remote server, your web app, within the database itself or even within multiple databases in a multimaster situation.

On the other hand, an auto incremented INT can only be safely generated within the primary database. Again, this might be okay if you have an application that will be intimately tied to that one backing DB server and scaling out is not something you are concerned with.

Sure, usage of GUIDs mean you have to have nightly reindexing processes. However, if you are using anything other than an auto incremented INT you should do that anyway. Heck, even with an INT as the primary it''s likely you have other indexes that need regenerated to deal with fragmentation. Therefore, using GUIDs doesn''t exactly add another problem because those tasks need to be performed regardless.

If you take a look at the larger apps out there you will notice something important: they all use Base64 encoded GUIDs as the keys. The reason for this is simple, usage of GUIDs enables you to scale out easily whereas there can be a lot of hoops to jump through when attempting to scale out INTs.

Our latest app goes through a period of heavy inserts that lasts for about a month. After that 90+% of the queries are all selects for reporting. To increase capacity I can bring up additional DB servers during this large insert period; and later easily merge those into a single DB for reporting. Attempting to do that with INTs would be an absolute nightmare.

Quite frankly, any time you cluster a database or setup replication the DB server is going to demand that you have GUIDs on the table anyway. So, if you think that your system might need to grow then pick the one that''s good.

This is a complex subject whether you realized it or not. Might fall under the section on this FAQ.

What kind of questions should I not ask here?

Avoid asking questions that are subjective, argumentative, or require extended discussion. This is a place for questions that can be answered!

This has been debated for years and will continue to be debated for years. The only hints of consensus I have seen is that the answers are somewhat predictable depending on if you are asking a OO guy (GUIDs are the only way to go!), a data modeler (Natural keys are the only way to go!), or a performance oriented DBA (INTs are the only way to go!).