database design - tutorial - ¿Qué tamaño elegir para una(n) columna varchar?

framework django python (8)

En una discusión ligeramente acalorada sobre TDWTF , surgió una pregunta sobre el tamaño de las columnas varchar en un DB.

Por ejemplo, tome un campo que contenga el nombre de una persona (solo nombre, sin apellido). Es bastante fácil ver que no será muy largo. La mayoría de las personas tienen nombres con menos de 10 caracteres, y pocos son los que tienen más de 20. Si hiciera su columna, por ejemplo, varchar (50), definitivamente tendría todos los nombres con los que se encontraría.

Sin embargo, para la mayoría de los DBMS, no hay diferencia en el tamaño o la velocidad, ya sea que realice una varchar (50) o una varchar (255).

Entonces, ¿por qué la gente trata de hacer sus columnas lo más pequeñas posible? Entiendo que, en algún caso, es posible que desee establecer un límite en la longitud de la cadena, pero sobre todo no es así. Y un margen más amplio solo será beneficioso si hay un caso raro de una persona con un nombre extremadamente largo.

Agregado: la gente desea referencias a la afirmación sobre "ninguna diferencia en tamaño o velocidad". DE ACUERDO. Aquí están:

Para MSSQL:

El tamaño de almacenamiento es la longitud real de los datos introducidos + 2 bytes.

Para MySQL:

L + 1 bytes si los valores de columna requieren 0 - 255 bytes, L + 2 bytes si los valores pueden requerir más de 255 bytes

No puedo encontrar documentación para Oracle y no he trabajado con otros DBMS. Pero no tengo ninguna razón para creer que sea diferente allí.

He escuchado que el optimizador de consultas tiene en cuenta la longitud de varchar, aunque no puedo encontrar una referencia.

Definir una longitud varchar ayuda a comunicar la intención. Cuanto más definidos estén los contraints, más confiables serán los datos.

La respuesta simple a esto, en mi opinión, es el hecho de que no puede usar esa columna como una clave de índice. Si necesita algún tipo de indexación, básicamente se le obliga a usar texto completo ... esto se refiere al uso de una columna varchar (max). En cualquier caso, las columnas de ''tamaño correcto'' tienen mucho sentido cada vez que [puede] querer aplicar cualquier indización; la actualización de columnas de longitud variable puede ser una maniobra costosa, ya que no se realiza en su lugar y puede / causará cierta fragmentación.

Todo lo relacionado con MS SQ-Server.

Responderé a su pregunta con una pregunta: Si no hay diferencia en el DBMS entre un varchar (50) y un varchar (255), ¿por qué el DBMS le permite hacer una distinción? ¿Por qué un DBMS simplemente no dice "usar varchar para hasta xxx caracteres, y texto / clob / etc. Para cualquier cosa sobre eso". Claro, quizás Microsoft / Oracle / IBM pueda mantener la definición de longitud por razones históricas, pero ¿qué pasa con DBMS como MySQL, que tiene múltiples backends de almacenamiento, por qué cada uno implementa longitudes de columna de caracteres definibles?

Si permite que la longitud de los datos sea superior a 255 y alguien se vincule a los datos a través de MS Access, los datos no se pueden utilizar para unir tablas (aparece como un campo de memo). Si los datos se exportan a Excel, estarán limitados a 255 caracteres por campo. La compatibilidad con otros programas debe considerarse al crear conjuntos de datos.
El control de calidad de los datos consiste en controlar los datos que ingresan a su entorno. ¿Qué necesitas para almacenar que tiene más de 255 caracteres? Hay veces en que los datos deben tener más de 255 caracteres, pero deben estar lejos y ser pocos entre ellos y deben usarse como información complementaria de apoyo para un campo que se puede usar para el análisis

Si va a imprimir etiquetas, normalmente desea que la cadena no tenga más de 35 caracteres. Es por eso que desea cierto control sobre el tamaño del Varchar que va a utilizar para aceptar las líneas que se van a utilizar para imprimir etiquetas.

Solo puedo hablar por Oracle. Un VARCHAR2 (50) y un VARCHAR2 (255) ocupan exactamente la misma cantidad de espacio y se desempeñan de manera idéntica, si ingresa el valor ''SMITH''.

Sin embargo, la razón por la que generalmente no es una buena idea repasar todas sus columnas textuales como VARCHAR2 (4000) es que la longitud de la columna es, efectivamente, otra restricción. Y las restricciones son la implementación en la base de datos de las reglas comerciales, por lo que definitivamente son algo que debe definirse en el lado de la base de datos.

Como por ejemplo. Usted define una restricción CHECK en una columna para que los valores que puede aceptar sean solo ''Y'' y ''N''. Eso evita que su aplicación tenga que lidiar con ''y'' y ''n'' o incluso con ''1'' y ''0''. La restricción de verificación garantiza que sus datos cumplan con los estándares esperados. El código de su aplicación puede hacer suposiciones válidas sobre la naturaleza de los datos con los que tiene que tratar.

La definición de la longitud de la columna está en el mismo barco. Declaras que algo es un VARCHAR2 (10) porque no quieres que acepte una entrada de ''ABC123ZYX456'' (¡por cualquier motivo!)

En Australia, defino que las columnas de ESTADO son varchar2 (3) porque no quiero que la gente escriba "Nueva Gales del Sur" o "Australia del Sur". La definición de la columna los obliga a ser ingresados como ''NSW'' y ''SA''. En ese sentido, un VARCHAR2 (3) es casi tanto una restricción de verificación como la especificación de una restricción CHECK IN (''NSW'', ''SA'', ''VIC'', etc.).

En resumen, las longitudes de columna adecuadas son una forma de codificar las reglas de negocios. Son otra forma de restricción. Traen todas las ventajas de las restricciones (y sufren muchos de los mismos inconvenientes). Y aseguran, hasta cierto punto, un grado de "limpieza de datos" con el que también ayudan las restricciones "adecuadas".

Tampoco compro el argumento de que es mejor pegar este tipo de cosas en la aplicación cliente porque es más fácil cambiar allí. Tienes 20,000 personas usando una aplicación, eso es 20,000 actualizaciones. Tienes una base de datos, esa es una actualización. El argumento de ''la aplicación del cliente más fácil de cambiar'', si es cierto, podría significar que la base de datos simplemente se tratará como un cubo de bits gigante con toda la lógica inteligente manejada en el código del cliente. Es una gran discusión, pero dado que todos los RDBMS le permiten definir restricciones y demás en la propia base de datos, está bastante claro que hay al menos un caso que vale la pena considerar que esa lógica fundamental pertenece al backend.

Una distinción importante es entre especificar un límite arbitrariamente grande [por ejemplo, VARCHAR(2000) ] y usar un tipo de datos que no requiere un límite [por ejemplo, VARCHAR(MAX) o TEXT ].

PostgreSQL basa todos sus VARCHAR longitud fija en su tipo TEXT límite, y decide dinámicamente por valor cómo almacenar el valor, incluido el almacenamiento fuera de la página. El especificador de longitud en este caso realmente es solo una restricción, y su uso está realmente desaconsejado. (ref)

Otros DBMS requieren que el usuario seleccione si requiere almacenamiento "sin límite", fuera de la página, generalmente con un costo asociado en comodidad y / o rendimiento.

Si hay una ventaja en el uso de VARCHAR(<n>) sobre VARCHAR(MAX) o TEXT , es necesario que seleccione un valor para <n> al diseñar sus tablas. Suponiendo que hay un ancho máximo de una fila de tabla o entrada de índice, deben aplicarse las siguientes restricciones:

<n> debe ser menor o igual que <max width>
Si <n> = <max width> , la tabla / índice puede tener solo 1 columna
en general, la tabla / índice solo puede tener <x> columnas donde (en promedio) <n> = <max width> / <x>

Por lo tanto, no es el caso que el valor de <n> actúe solo como una restricción, y la elección de <n> debe ser parte del diseño. (Incluso si no hay un límite rígido en su DBMS, puede haber razones de rendimiento para mantener el ancho dentro de un cierto límite).

Puede usar las reglas anteriores para asignar un valor máximo de <n> , en función de la arquitectura esperada de su tabla (teniendo en cuenta el impacto de los cambios futuros). Sin embargo, tiene más sentido definir el valor mínimo de <n> , en función de los datos esperados en cada columna. Lo más probable es que se expanda al "número redondo" más cercano; por ejemplo, siempre usará VARCHAR(10) , VARCHAR(50) , VARCHAR(200) o VARCHAR(1000) , el que mejor se ajuste.

Entonces, ¿por qué la gente trata de hacer sus columnas lo más pequeñas posible? No creo en hacerlos lo más pequeños posible, sino en dimensionarlos adecuadamente. Algunas razones para hacer (n) varchars más pequeños que grandes:

1) Con un campo más grande, todos los clientes que usan la base de datos deben poder manejar el tamaño completo. Por ejemplo, tome un sistema que tenga una dirección de los Estados Unidos con 255 caracteres por cada campo: (Al igual que TDWTF a la que hace referencia, creo).

Nombre de pila
Apellido
Dirección Línea 1
Dirección Línea 2
Ciudad
Estado
Código postal

Ahora sus pantallas de ingreso de datos deberán permitir y mostrar 255 caracteres por campo. No es difícil, pero es poco probable que se vea bien con campos más grandes. Al imprimir facturas, necesitará una lógica de corte de línea para manejar los campos grandes. Dependiendo de la herramienta, no es tan difícil.

Pero no querría el problema de formatear la dirección de un sobre que podría tener 255 caracteres para cada uno de esos campos o simplemente cualquiera de esos campos. ¿Vas a truncar si el campo es demasiado largo para encajar? Gran persona tiene la Línea de dirección 1 de "Número de Casa Número de Amenaza ... bla bla bla ... Número de apartamento 111." Y le quitará el número importante de apartamento. ¿Vas a envolver? ¿Cuánto cuesta? ¿Qué pasa si simplemente no puede caber en la pequeña caja de espacio en la envoltura? ¿Levantar una excepción y hacer que alguien la escriba?

2) Mientras que 10 caracteres de datos guardados en varchar (50) versus varchar (255) no afectan el tamaño o la velocidad, permitir 255 caracteres permite que se tome más espacio. Y si todos los campos son tan grandes, podría alcanzar los límites de tamaño en SQL Server 2000. (No he leído en 2005 y 2008 para ver si pueden manejar filas mayores de una página). Y con Oracle, los tamaños más grandes permiten la fila el encadenamiento sucederá si alguien realmente usa todos los caracteres disponibles.

3) Los índices tienen límites de tamaño más estrictos que las páginas de hoja. Puede excluir índices, especialmente índices compuestos, si crea sus varchars demasiado grandes.

Por otro lado, tengo una línea larga 1 para mi dirección y me han frustrado los sitios web que no permiten que se escriba todo.