¿De qué lado tiene la razón? Ni. Ambas teclas naturales y sustitutos tienen su propio lugar, y un desarrollador que no usa tanto como la situación lo requiera está estafando a sí mismo y sus aplicaciones.
Definiciones
Una clave natural es simplemente una columna o conjunto de columnas de una tabla que identifica de forma única cada fila. teclas naturales son una característica de la información , no la base de datos, y por lo tanto han significado negocio por su cuenta. Muy a menudo una clave natural es más que una sola columna. Por ejemplo, la clave natural para una tabla de direcciones pueden ser las cinco columnas: número de la calle, calle, ciudad, estado y código postal.¿Qué es una clave sustituta? La mayoría de las personas lo definen como una variación de "un valor generado por el sistema utilizado para identificar de forma única una fila". A diferencia de las teclas naturales, sustitutos no tienen sentido de negocios. En SQL Server, con mucho, la técnica más común para la generación de valores de sustitución es la columna IDENTITY en todas partes.
Si bien la definición anterior es cierto, hay otra parte muy importante para ella. El valor de una madre sustituta no debe ser expuesto al mundo exterior. Los usuarios nunca se debe permitir que ver la clave, en cualquier condición. Mostrar el valor de una clave sustituta en un informe, deje que se pueden ver en un formulario o incluso se utiliza como término de búsqueda - todos estos son prohibidos. Una vez que se exponga una clave sustituta, de inmediato comienza a adquirir sentido de negocios.
Smart Keys: Lo peor de ambos mundos
Una tecla inteligente es una clave artificial con una o más partes que contienen el sentido de negocio. Por ejemplo, una tabla de empleados, donde cada clave principal comienza con la inicial del empleado ("JD1001" de John Doe), o una tabla de productos de pintura, donde la clave identifica el tamaño de una lata, el color y tipo de pintura, es decir, "1G -RED-LATEX ".teclas inteligentes son una especie de híbrido entre las teclas naturales y de alquiler. Son seductor atractivo para muchos desarrolladores, pero se debe evitar como la peste. Tienden a hacer su diseño muy frágil y sujeto al fracaso como cambiar las reglas de negocio.
Nota: si los datos que ya contiene los códigos de producto significativas o claves tales como los descritos anteriormente, a continuación, son simplemente las teclas naturales y la advertencia anterior no se aplica. Es una tecla inteligente sólo cuando el valor es construido por el desarrollador.
Beneficios de las teclas naturales
Una clave natural es ... bueno, natural. Desde sus valores ya existentes en los datos, utilizando una clave natural significa que usted no tiene que añadir y mantener una nueva columna. Esto también significa mesas más pequeñas y menos requisitos de almacenamiento. A medida que más filas caben en una página de base de datos, a veces puede significar un mayor rendimiento. (También puede significar menos más sobre esto más adelante). Sin embargo, en términos prácticos, el ahorro de espacio es menor, a excepción de las tablas muy estrecho. Usando una clave sustituta por lo general significa un índice adicional se requiere, sin embargo.Generación de valores secuenciales clave es inherentemente un proceso en serie, así que usar una clave natural en lugar de una columna de identidad puede ser un aumento de rendimiento para las inserciones, especialmente en entornos OLTP.
Dado que los valores naturales clave se utilizan como claves externas de las tablas secundarias, puede significar la eliminación de las combinaciones para las consultas que no requieren de otras columnas de la matriz que no sea la clave natural.
Uno de los beneficios de las claves naturales a menudo reclamado por sus defensores es que puede ayudar en la auto-documentación de su esquema de base de datos. Explícitamente el nombre de cada tecla de documentos natural lo que identifica específicamente cada fila en una tabla, y unirse en las teclas naturales ayuda a identificar las relaciones naturales entre las tablas. Este argumento se basa en la elegancia hace un firme llamamiento a las personas que trabajan en el ámbito académico, ya que puede o no tener mucho valor para los desarrolladores que trabajan en el polvo y la suciedad de los sistemas reales de producción.
Beneficios de claves suplentes
Puesto que los valores sustitutos son controlados por el sistema, usted nunca tendrá que preocuparse por duplicado, que falta, o cambiar los valores. También son una manera fácil y confiable para unir tablas, cuando la escritura de consultas, que nunca tendrá que preocuparse de recordar la combinación de las columnas es la clave natural. Sin embargo, algunos de estos beneficios son menos convincentes de lo que parecen. Voy a discutir por separado cada caso para el uso de un sustituto, y si no se sostiene.Cuando no hay una clave natural en la tabla existe - Sí .
Si la tabla no tiene identificador único, a continuación, debe crear uno. tablas sin clave, en general, una muy mala idea. Hay excepciones como el registro o tablas resumen en las filas que se sólo se inserta, no actualizada. De lo contrario, si la tabla no tiene una única clave para crear uno.
Cuando la clave natural puede cambiar-A veces.
Inmutabilidad es ciertamente una conveniente función de una clave, pero de ninguna manera es un requisito. Usando esto como excusa es la marca de un desarrollador de perezoso. Datos de cambios. Si los cambios de clave, actualización. Si se está utilizando como clave externa en otra tabla - mesa de actualización que también. actualizaciones en cascada existen por una razón, después de todo.
Obviamente, si una tecla cambia muy a menudo, o se utilizará como FK para muchos otros cuadros, no puede haber implicaciones de rendimiento o la concurrencia de lo que es la clave principal. En este caso, quiero considerar un sustituto, y utilizarlo en su caso.
Hay un caso particular en que la estabilidad de una clave sustituta en realidad va en contra de usted. Para las tablas de búsqueda, especialmente los que contienen las opciones de selección para determinados campos, los cambios en el valor de búsqueda a menudo no están destinados a ser conectados en cascada en tablas secundarias. Por ejemplo, una aplicación puede almacenar la "fuente de referencia" para los nuevos clientes o clientes potenciales de comercialización, ya sea que se generaron a través de un anuncio en un periódico o una revista, una entrada de páginas amarillas, de boca en boca, etc Estos códigos de referencia pueden ser muy específicas , y normalmente se almacena en una tabla de búsqueda. Una vez establecido, el código debe ser preservado históricamente, incluso si el valor de la tabla original de las operaciones de búsqueda se actualiza o se elimina. Este comportamiento es muy difícil de lograr con una clave sustituta, pero trivial con una clave natural.
principales valores naturales ¿Cuándo puede faltar o estar duplicadas - n .
Este es probablemente el aspecto más incomprendido del debate vs sustituto natural. teclas naturales son únicos por definición. Si no es un grave error por un valor que falta o duplicado, a continuación, que no es natural de clave y valor para empezar. Y si es un error, entonces usted es casi siempre mejor que el error de captura a nivel de base de datos, en lugar de permitir que los malos datos en su base de datos.
Como ejemplo, considere una tabla de empleados clave de Número de Seguro Social. Los usuarios se quejan de la base de datos arroja errores cuando no tiene un Número de Seguro Social o por error entrar en un duplicado. Así que sustituir el PK número de Seguro Social con un suplente y con aire de suficiencia concluir que ha resuelto el problema. Pero usted tiene? Ahora, algunos empleados no tienen números de Seguro Social, y el módulo de contabilidad comienza a fallar al imprimir los registros de impuestos ... o peor, recopila todas las entradas NULL número de Seguro Social en conjunto, la presentación de informes como un solo empleado. La función de búsqueda se inicia la devolución del mal filas debido a que algunos empleados están compartiendo el mismo número de Seguro Social, y el alquiler de nuevo en la sala de correo recibe sueldo del jefe, porque alguien de la FC accidentalmente cortado y pegado un número de Seguro Social.
En realidad, todo lo que hemos hecho es corto circuito a las garantías de integridad de datos en la base de datos, y pasar la responsabilidad del problema hasta el nivel de aplicación. Mal movimiento.
Este tipo de problemas existen porque la mayoría de las tablas tienen un requisito de singularidad a nivel ción. Una clave sustituta sólo resuelve el problema de la unicidad en la base de datos de nivel, pero los usuarios (que no puede y no debe ver el valor sustituto) todavía no tienen una manera de identificar de forma única cada registro. Esto también explica por qué, cuando, incluso si usted decide usar una clave sustituta, lo normal es que desee agregar también una restricción única en la clave natural original, ya que la singularidad es que ya no se aplican de forma automática por el PK.
Pero espera un minuto! ¿Qué pasa si las reglas de negocio específicamente requieren que los empleados pueden entrar antes de tener sus datos Número de Seguro Social? ¿O qué si la tabla tiene empleados en el extranjero que no puede tener un número de seguro social a todos? ¿Significa eso que no se puede utilizar una clave natural? Tal vez. Una posibilidad es asignar los valores de su propia y única en estos casos. Un sistema que he visto utilizado genera aleatoriamente valores alfabéticos para un SSN temporal, mientras que el valor numérico estándar identificado un "real". Mejor aún es examinar la tabla de alguna otra columna o columnas que se pueden utilizar como una clave natural. O tal vez usted realmente quiere borrar claves naturales en conjunto. El punto aquí no es que los sustitutos no deben usarse nunca, sino simplemente que si la clave natural no es única, va a tener problemas más allá de lo que un sustituto va a resolver.
Cuando la clave natural es muy amplia, o una combinación de varias columnas - A veces .
Amplia claves para hacer que los índices de grasa. los índices de grasa tiene implicaciones de rendimiento. Una clave muy amplio puede perjudicar el rendimiento mucho más que el espacio adicional requerido por un sustituto. Sustitución de una clave compuesta con un sustituto también simplifica las consultas, pero esto no debe ser una consideración primordial. Es la falta de forma para reemplazar la clave natural de un CHAR dos (2) columnas con am INT IDENTIDAD, por la sencilla razón de lo que hace que las consultas más bonita.
Un caso común una clave natural de varias columnas siempre se debe aplicar es la tabla de unión llamada: una tabla utilizada para poner en práctica una relación de muchos-entre muchas otras dos tablas. La mayoría de las tablas de conexiones sólo tienen dos columnas, cada una FK de nuevo a una tabla primaria. La combinación de estos dos FK es en sí mismo la clave principal de la tabla. Adición de un sustituto para una tabla como ésta es buscar problemas.
Por motivos de rendimiento .
Esta es la pregunta más difícil de todos. Sustitución de una clave de ancho, con un valor estrecho significa índices más pequeños, y más valores recuperados de cada página de índice de lectura. Esto hace aumentar el rendimiento. Sin embargo, lo más probable mantener el índice en la clave natural (para aplicar la unicidad si nada) y eso significa otro índice de mantener. Si la tabla es muy estrecha, la columna adicional para la madre sustituta puede afectar notablemente los requisitos de rendimiento y almacenamiento. Por último, algunas consultas que no puede haber requerido que se une a una clave externa naturales ahora posible que los necesite. Por ejemplo, nuestro número de seguro social del empleado ejemplo, podría tener una tabla secundaria que contiene las horas reportadas trabajado:
Tabla: ReportedHours | |
Start_time | DATETIME |
Stop_time | DATETIME |
EmployeeID | (Clave externa para la tabla Empleado) |
Si el FK EmployeeID es número de Seguro Social, entonces podemos obtener una lista de horas totales por número de Seguro Social de esta tabla solo:
SELECCIONAR EmployeeID , SUM ( DATEDIFF ( hr , stopTime , StartTime ))
DE ReportedHours
GRUPO DE EmployeeID
Con IdEmpleado como una clave sustituta, sin embargo, debemos unir de nuevo a la tabla Empleado:SELECCIONAR número de Seguro Social , SUM ( DATEDIFF ( hr , stopTime , StartTime ))
DE ReportedHours h
UNETE e Empleados SOBRE h . EmployeeID = e . EmployeeID
GRUPO DE electrónico . SSN
Performance Testing
Three different examples highlighting three different aspects of the performance issue are tested. As you will see, neither synthetic nor natural keys win in all cases. Note: the examples shown here are ‘corner cases’, designed to highlight performance differences. In real world databases, the differences you see are likely to be smaller than those shown here.Test Case 1: OLTP Data Insertion
A sample employee table is created, using SSN as primary key. Two client sessions ( on separate machines) are simultaneously started, each inserting 100,000 rows of random test data. The test is then rerun with an IDENTITY column as primary key, and a unique constraint added on SSN.Test Table 1: Natural Key
CREATE TABLE Employees
(
SSN CHAR(9) PRIMARY KEY,
Firstname VARCHAR(50),
Lastname VARCHAR(50),
Date1 DATETIME NOT NULL DEFAULT GETDATE(),
Int1 INTEGER NOT NULL DEFAULT 0,
Char1 Varchar(50),
)
Test Table 2: Surrogate KeyCREATE TABLE Employees
(
EmployeeID INT IDENTITY PRIMARY KEY,
SSN CHAR(9) UNIQUE NOT NULL,
Firstname VARCHAR(50),
Lastname VARCHAR(50),
Date1 DATETIME NOT NULL DEFAULT GETDATE(),
Int1 INTEGER NOT NULL DEFAULT 0,
Char1 Varchar(50),
)
Test Results (average of three runs)· Natural Key Insert: 39.1 sec.
· Surrogate Key Insert: 46.5 sec. (19% slower)
Before I ran this test, I expected the vast majority of any performance differential would be due maintaining two indexes, rather than one. However, when I reran it without the unique constraint on SSN, the difference was almost exactly half the original, meaning contention on the IDENTITY column is a significant factor. · Surrogate Key Insert: 46.5 sec. (19% slower)
Test Case #2: Narrowing a Wide Index
A query is used to join a child table to its parent by a 40-byte three-column foreign key. The child table contains one million rows, and the parent 300K rows. Key values are created randomly. The test is then rerun using a 4-byte IDENTITY column to perform the JOIN. To force an index seek (rather than an index scan) a WHERE clause is used to limit rows retrieved to 1% of the table total.
Table Schema:
CREATE TABLE Parent
(
SurrogateKey INT IDENTITY NOT NULL,
NatKeyPart1 CHAR(32) NOT NULL,
NatKeyPart2 INT NOT NULL,
NatKeyPart3 INT NOT NULL,
MiscData VARCHAR(100),
)
CREATE INDEX ix_Parent ON Parent(ID)
CREATE INDEX ix_ParentParts ON Parent(KeyPart1,KeyPart2,KeyPart3)
CREATE TABLE Child
(
SurrogateKey INT NOT NULL,
NatKeyPart1 CHAR(32) NOT NULL,
NatKeyPart2 INT NOT NULL,
NatKeyPart3 INT NOT NULL,
ChildData VARCHAR(100) PRIMARY KEY CLUSTERED
)
· Natural Key Insert: 11.1 sec. (21% slower)
· Surrogate Key Insert: 9.2 sec.
· Surrogate Key Insert: 9.2 sec.
Test Case #3: Join Elimination via Natural Key
The same data and methodology from Test Case #2 are used. The difference here is that the query references only natural key values from the Parent table, rather than all columns. This means when the natural key is used as the foreign key, no join to the Parent table is necessary; the query can be fully serviced by the Child table.Test Results (average of three runs):
· Natural Key Insert: 2.9 sec
· Surrogate Key Insert: 9.2 sec. (320% slower)
· Surrogate Key Insert: 9.2 sec. (320% slower)
No hay comentarios:
Publicar un comentario