Diseño de la database y el uso de keys primarias no numéricas

Actualmente estoy en el process de diseñar las tablas de la database para una aplicación de administración de clientes y sitios web. Mi pregunta es respecto al uso de keys primarias como partes funcionales de una tabla (y no asignar numbers de "ID" a cada tabla simplemente porque).

Por ejemplo, aquí hay cuatro tablas relacionadas de la database hasta ahora, una de las cuales usa el número de key principal tradicional, las otras que usan nombres únicos como key principal:

-- -- website -- CREATE TABLE IF NOT EXISTS `website` ( `name` varchar(126) NOT NULL, `client_id` int(11) NOT NULL, `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP, `notes` text NOT NULL, `website_status` varchar(26) NOT NULL, PRIMARY KEY (`name`), KEY `client_id` (`client_id`), KEY `website_status` (`website_status`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- website_status -- CREATE TABLE IF NOT EXISTS `website_status` ( `name` varchar(26) NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `website_status` (`name`) VALUES ('demo'), ('disabled'), ('live'), ('purchased'), ('transfenetworking'); -- -- client -- CREATE TABLE IF NOT EXISTS `client` ( `id` int(11) NOT NULL auto_increment, `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP, `client_status` varchar(26) NOT NULL, `firstname` varchar(26) NOT NULL, `lastname` varchar(46) NOT NULL, `address` varchar(78) NOT NULL, `city` varchar(56) NOT NULL, `state` varchar(2) NOT NULL, `zip` int(11) NOT NULL, `country` varchar(3) NOT NULL, `phone` text NOT NULL, `email` varchar(78) NOT NULL, `notes` text NOT NULL, PRIMARY KEY (`id`), KEY `client_status` (`client_status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- client_status --- CREATE TABLE IF NOT EXISTS `client_status` ( `name` varchar(26) NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `client_status` (`name`) VALUES ('affiliate'), ('customer'), ('demo'), ('disabled'), ('reseller'); 

Como puede ver, 3 de las 4 tablas usan su 'nombre' como la key principal. Sé que estos siempre serán únicos. En 2 de los casos (las tablas * _status) básicamente estoy usando un reemploop dynamic para ENUM, ya que las opciones de estado podrían cambiar en el futuro, y para la tabla 'website', sé que el 'nombre' del website siempre ser único.

Me pregunto si esto es lógico, si me deshago de las ID de las tablas cuando sé que el nombre siempre será un identificador único o una receta para el desastre. No soy un DBA experimentado, por lo que cualquier comentario, crítica, etc. sería extremadamente útil.

¡Gracias por tomarse el time de leer esto!

Hay 2 razones por las que siempre agregaría un número de identificación a una tabla de búsqueda / ENUM:

  1. Si está haciendo reference a una tabla de una sola columna con el nombre, entonces puede ser mejor si utiliza una restricción
  2. ¿Qué sucede si quieres cambiar el nombre de una de las inputs de client_status? por ejemplo, si desea cambiar el nombre de 'afiliado' a 'usuario afiliado', deberá actualizar la tabla del cliente, que no debería ser necesaria. El número de identificación sirve como reference y el nombre es la descripción.

En la tabla del website, si está seguro de que el nombre será único, está bien utilizarlo como key principal. Personalmente, aún asignaría una ID numérica ya que networkinguce el espacio utilizado en las tablas de keys externas y me resulta más fácil de administrar.

EDITAR: Como se indicó anteriormente, se encontrará con problemas si se renombra el nombre del website. Al hacer de esta la key principal, será muy difícil, si no imposible, que esto se modifique en una date posterior.

Al hacer las PRIMARY KEY naturales, asegúrese de que su unicidad esté bajo su control.

Si está absolutamente seguro de que nunca tendrá una violación de singularidad, entonces está bien usar estos valores como PRIMARY KEY .

Dado que usted y usted solo generan y utilizan client_status y client_status , es aceptable usarlos como PRIMARY KEY , aunque tener una key larga puede afectar el performance.

website nombre del website parece estar bajo el control del mundo exterior, es por eso que lo convertiría en un campo simple. ¿Qué pasa si quieren cambiar el nombre de su website ?

Los contraejemplos serían SSN y códigos postales: no eres tú quien los genera y no hay garantía de que nunca se dupliquen.

Kimberly Tripp tiene una excelente serie de artículos de blog ( GUIDs como PRIMARY KEYs y / o la key de clustering y The Clustenetworking Index Debate Continues ) sobre el tema de crear índices agrupados y elegir la key principal (cuestiones relacionadas, pero no siempre exactamente iguales) ) Su recomendación es que un índice agrupado / key principal debe ser:

  1. Único (de lo contrario inútil como una key)
  2. Estrecha (la key se usa en todos los índices no agrupados y en las relaciones de key externa)
  3. Estático (no desea tener que cambiar todos los loggings relacionados)
  4. Siempre aumenta (para que los loggings nuevos siempre se agreguen al final de la tabla y no tengan que insertse en el medio)

Usar "Nombre" como su key, mientras que parece satisfacer al # 1, no satisface CUALQUIERA de las otras tres.

Incluso para su tabla de "búsqueda", ¿qué ocurre si su jefe decide cambiar todos los afiliados a socios ? Tendrá que modificar todas las filas en la database que usan este valor.

Desde una perspectiva de performance, probablemente estoy más preocupado de que una key sea estrecha . Si el nombre de su website es en realidad una URL larga, eso podría realmente ensanchar el tamaño de cualquier índice no agrupado, y todas las tablas que lo usan como una key externa.

Además de todos los otros puntos excelentes que ya se han hecho, agregaría una palabra más de precaución contra el uso de campos grandes como clustering de keys en SQL Server (si no está usando SQL Server, probablemente esto no se aplique a usted) .

Agrego esto porque en SQL Server, la key principal en una tabla de forma pnetworkingeterminada también es la key de clúster (puede cambiar eso, si lo desea y saber al respecto, pero la mayoría de los casos, no está hecho).

La key de clúster que determina el order físico de la tabla de SQL Server también se agrega a cada índice no agrupado en esa tabla. Si tiene solo unos cientos o miles de filas y uno o dos índices, eso no es gran cosa. Pero si tiene tablas realmente grandes con millones de filas y potencialmente muchos índices para acelerar las consultas, esto causará mucho espacio en el disco y la memory del server se desperdiciará innecesariamente.

Por ejemplo, si su tabla tiene 10 millones de filas, 10 índices no agrupados y su key de agrupamiento es de 26 bytes en lugar de 4 (para una INT), entonces está desperdiciando 10 millones. por 10 por 22 bytes para un total de 2.2 billones de bytes (o 2.2 GBytes aprox.) – ¡eso ya no es un maní!

De nuevo, esto solo se aplica a SQL Server, y solo si tiene tablas realmente grandes con muchos índices no agrupados.

Bagazo

"Si está absolutamente seguro de que nunca tendrá una violación de singularidad, entonces está bien usar estos valores como PRIMARY KEY".

Si está absolutamente seguro de que nunca tendrá una violación de singularidad, no se moleste en definir la key.

Personalmente, creo que tendrás problemas para usar esta idea. A medida que terminas con más relaciones padre-hijo, terminas con una gran cantidad de trabajo cuando cambian los nombres (como siempre tarde o temprano). Puede haber un gran golpe de performance cuando se tiene que actualizar una tabla hija que tiene miles de filas cuando cambia el nombre del website. Y debe planificar cómo asegurarse de que esos cambios sucedan. De lo contrario, el nombre del website cambia (oops dejamos que el nombre caduque y alguien más lo compró.) O bien se rompe debido a la restricción de key externa o necesita hacerlo de manera automatizada (actualización en cascada) para propagar el cambio a través del sistema. Si usa actualizaciones en cascada, puede detener repentinamente el sistema mientras se procesa un chage grande. Esto no se considera algo bueno. Realmente es más efectivo y eficiente usar identificadores para relaciones y luego poner índices únicos en el campo de nombre para asegurar que se mantengan únicos. El layout de la database necesita considerar el mantenimiento de la integridad de los datos y cómo eso afectará el performance.

Otra cosa a considerar es que los sitios web tienden a ser más largos que unos pocos caracteres. Esto significa que la diferencia de performance entre el uso de un campo de identificación para las uniones y el nombre para las uniones podría ser bastante significativo. Tienes que pensar en estas cosas en la fase de layout, ya que es demasiado tarde para cambiar a una ID cuando tienes un sistema de producción con millones de loggings que se agota y la solución es reestructurar completamente la database y volver a escribir todo el SQL código. No es algo que pueda solucionar en quince minutos para que el sitio vuelva a funcionar.

Esto simplemente parece una muy mala idea. ¿Qué sucede si necesita cambiar el valor de la enumeración? La idea es convertirla en una database relacional y no en un set de files planos. En este punto, ¿por qué tener la tabla client_status? Además, si está utilizando los datos en una aplicación, utilizando un tipo como GUID o INT, puede validar el tipo y evitar datos incorrectos (en la medida en que se valida el tipo). Por lo tanto, es otra de muchas líneas para evitar piratería.

Yo diría que una database que es resistente a la corrupción, incluso si funciona un poco más lento, es mejor que una que no lo es.

En general, las keys sustitutas (como los identificadores numéricos arbitrarios) socavan la integridad de la database. Las keys primarias son la principal forma de identificar filas en la database; si los valores de la key primaria no son significativos, la restricción no es significativa. Cualquier key foránea que haga reference a keys primarias sustitutas también es, por lo tanto, sospechosa. Siempre que tenga que recuperar, actualizar o eliminar filas individuales (y se garantiza que solo afectará a una), la key principal (u otra key candidata) es lo que debe usar; tener que calcular qué es un valor de key sustituta cuando hay una key alternativa significativa es un paso networkingundante y potencialmente peligroso para los usuarios y las aplicaciones.

Incluso si eso significa usar una key compuesta para garantizar la singularidad, recomendaría utilizar un set de attributes significativo y natural como key principal, siempre que sea posible. Si necesita registrar los attributes de todos modos, ¿por qué agregar otro? Dicho esto, las keys sustitutas están bien cuando no hay una key natural, estable, concisa, garantizada para ser única (por ejemplo, para las personas).

También podría considerar usar la compression de key de índice, si su DBMS lo admite. Esto puede ser muy efectivo, especialmente para índices en keys compuestas (piense en las estructuras de datos), y especialmente si los attributes less selectivos pueden aparecer primero en el índice.

Creo que estoy de acuerdo con Cheduardo. Han pasado 25 años desde que tomé un curso sobre layout de bases de datos, pero recuerdo que me dijeron que los motores de database pueden administrar y cargar de manera más eficiente los índices que usan keys de caracteres. Los comentarios sobre la database que tiene que actualizar miles de loggings cuando se cambia una key y en todo el espacio adicional ocupado por las keys más largas y luego tener que ser transferidos a través de los sistemas, supone que la key está realmente almacenada en los loggings y que no tiene que ser transferido a través de los sistemas de todos modos. Si crea un índice en una columna (s) de una tabla, no creo que el valor esté almacenado en los loggings de la tabla (a less que configure alguna opción para hacerlo).

Si tiene una key natural para una tabla, incluso si se cambia ocasionalmente, la creación de otra key crea una networkingundancia que podría ocasionar problemas de integridad de los datos y realmente crea aún más información que debe almacenarse y transferirse a través de los sistemas. Trabajo para un equipo que decidió almacenar la configuration de la aplicación local en la database. Tienen una columna de identidad para cada configuration, un nombre de sección, un nombre de key y un valor de key. Tienen un procedimiento almacenado (otra guerra santa) para save una configuration que garantiza que no aparezca dos veces. Todavía tengo que encontrar un caso en el que use la identificación de una configuration. Sin embargo, terminé con varios loggings con la misma sección y el mismo nombre de key que causaron el fracaso de mi aplicación. Y sí, sé que podría haberse evitado definiendo una restricción en las columnas.

Aquí se deben considerar algunos puntos antes de decidir las keys en la tabla

  • La key numérica es más adecuada cuando utiliza references (keys externas), ya que no usa keys externas, está bien que en su caso use una key no numérica.

  • La key no numérica usa más espacio que las teclas numéricas, puede disminuir el performance.

  • Las teclas numéricas hacen que db parezca más simple de entender (usted puede saber fácilmente no de filas simplemente mirando la última fila)