usuario tipos tipo telefono tabla sirven que por para modificar ejemplos definidos datos dato campos sql database design user-defined-fields

sql - tipos - ¿Cómo diseñar una base de datos para campos definidos por el usuario?



tipos de datos en mysql workbench (13)

  1. Cree varias tablas UDF, una por tipo de datos. Entonces tendríamos tablas para UDFStrings, UDFDates, etc. Probablemente haría lo mismo que # 2 y autogeneraría una Vista cada vez que se agregue un nuevo campo

De acuerdo con mi investigación, varias tablas basadas en el tipo de datos no te ayudarán en el rendimiento. Especialmente si tiene datos masivos, como registros de 20K o 25K con más de 50 UDF. El rendimiento fue el peor.

Deberías ir con una sola tabla con múltiples columnas como:

varchar Name varchar Type decimal NumberValue varchar StringValue date DateValue

Mis requisitos son:

  • Necesita ser capaz de agregar dinámicamente campos definidos por el usuario de cualquier tipo de datos
  • Necesidad de poder consultar UDFs rápidamente
  • Necesidad de poder hacer cálculos en UDF basados ​​en el tipo de datos
  • Necesidad de poder ordenar UDF en función del tipo de datos

Otra información:

  • Estoy buscando un rendimiento principalmente
  • Hay unos pocos millones de registros maestros que pueden tener datos UDF adjuntos
  • La última vez que revisé, había más de 50mil de registros UDF en nuestra base de datos actual
  • La mayoría de las veces, un UDF solo se adjunta a unos pocos miles de registros maestros, no todos
  • Las UDF no se unen ni se usan como claves. Solo son datos utilizados para consultas o informes

Opciones:

  1. Crea una gran tabla con StringValue1, StringValue2 ... IntValue1, IntValue2, ... etc. Odio esta idea, pero la consideraré si alguien puede decirme que es mejor que otras ideas y por qué.

  2. Cree una tabla dinámica que agregue una nueva columna según demanda según sea necesario. Tampoco me gusta esta idea, ya que creo que el rendimiento sería lento a menos que hayas indexado cada columna.

  3. Cree una tabla única que contenga UDFName, UDFDataType y Value. Cuando se agrega una nueva UDF, genere una vista que extraiga solo esa información y la analice en el tipo especificado. Los elementos que no cumplen los criterios de análisis devuelven NULL.

  4. Cree varias tablas UDF, una por tipo de datos. Entonces tendríamos tablas para UDFStrings, UDFDates, etc. Probablemente haría lo mismo que # 2 y autogeneraría una Vista cada vez que se agregue un nuevo campo

  5. XML DataTypes? No he trabajado con estos antes, pero los he visto mencionar. No estoy seguro si me darían los resultados que quiero, especialmente con el rendimiento.

  6. ¿Algo más?

En los comentarios, lo vi diciendo que los campos UDF son para volcar datos importados que el usuario no ha mapeado correctamente.

Quizás otra opción sea rastrear el número de UDF creadas por cada usuario y forzarlas a volver a usar los campos diciendo que pueden usar 6 (u otro límite igualmente aleatorio) campos personalizados.

Cuando te enfrentas a un problema de estructuración de la base de datos como este, a menudo es mejor volver al diseño básico de la aplicación (importar el sistema en tu caso) y poner algunas restricciones más sobre él.

Ahora lo que haría es la opción 4 (EDITAR) con la adición de un enlace a los usuarios:

general_data_table id ... udfs_linked_table id general_data_id udf_id udfs_table id name type owner_id --> Use this to filter for the current user and limit their UDFs string_link_id --> link table for string fields int_link_id type_link_id

Ahora asegúrese de hacer vistas para optimizar el rendimiento y obtener sus índices correctos. Este nivel de normalización reduce la huella de DB, pero su aplicación es más compleja.

Esta es una situación problemática, y ninguna de las soluciones parece "correcta". Sin embargo, la opción 1 es probablemente la mejor tanto en términos de simplicidad como en términos de rendimiento.

Esta es también la solución utilizada en algunas aplicaciones empresariales comerciales.

EDITAR

Otra opción que está disponible ahora, pero que no existía (o al menos no estaba madura) cuando se formuló la pregunta original es usar los campos json en el DB.

muchos DB relacionales ahora admiten campos basados ​​en json (que pueden incluir una lista dinámica de subcampos) y permiten consultarlos

postgress

mysql

Esto suena como un problema que podría resolverse mejor con una solución no relacional, como MongoDB o CouchDB.

Ambos permiten la expansión de esquema dinámico al tiempo que le permite mantener la integridad de tupla que busca.

Estoy de acuerdo con Bill Karwin, el modelo de EAV no es un enfoque de rendimiento para usted. El uso de pares nombre-valor en un sistema relacional no es intrínsecamente malo, pero solo funciona bien cuando el par nombre-valor forma una tupla completa de información. Cuando se usa te obliga a reconstruir dinámicamente una tabla en tiempo de ejecución, todo tipo de cosas comienzan a ponerse difíciles. La consulta se convierte en un ejercicio de mantenimiento de pivote o lo obliga a empujar la reconstrucción de la tupla hacia la capa de objeto.

No puede determinar si un valor nulo o faltante es una entrada válida o falta de entrada sin incrustar reglas de esquema en su capa de objeto.

Pierde la capacidad de administrar eficientemente su esquema. ¿Es un varchar de 100 caracteres el tipo correcto para el campo "valor"? 200 caracteres? ¿Debería ser nvarchar en su lugar? Puede ser un compromiso difícil y uno que termina con la necesidad de poner límites artificiales a la naturaleza dinámica de su conjunto. Algo así como "solo puede tener x campos definidos por el usuario y cada uno solo puede tener caracteres y de longitud".

Con una solución orientada a documentos, como MongoDB o CouchDB, usted mantiene todos los atributos asociados con un usuario dentro de una sola tupla. Dado que las uniones no son un problema, la vida es feliz, ya que ninguno de estos dos le va bien con las uniones, a pesar de la exageración. Sus usuarios pueden definir tantos atributos como quieran (o permitirán) a la medida que no sean difíciles de administrar hasta que alcancen aproximadamente 4MB.

Si tiene datos que requieren integridad de nivel ACID, podría considerar dividir la solución, con los datos de alta integridad en su base de datos relacional y los datos dinámicos que viven en una tienda no relacional.

He written sobre este problema. La solución más común es el antipatrón Entity-Attribute-Value, que es similar a lo que describe en su opción n. ° 3. Evita este diseño como la peste .

Lo que uso para esta solución cuando necesito campos personalizados verdaderamente dinámicos es almacenarlos en un blob de XML, para poder agregar nuevos campos en cualquier momento. Pero para hacerlo más rápido, también cree tablas adicionales para cada campo que necesite buscar u ordenar (no tiene una tabla por campo, solo una tabla por campo de búsqueda ). Esto a veces se llama un diseño de índice invertido.

Puede leer un artículo interesante de 2009 sobre esta solución aquí: http://backchannel.org/blog/friendfeed-schemaless-mysql

O puede usar una base de datos orientada a documentos, donde se espera que tenga campos personalizados por documento. Yo elegiría Solr .

He logrado esto con mucho éxito en el pasado sin usar ninguna de estas opciones (opción 6? :)).

Creo un modelo para que los usuarios jueguen (almacenar como xml y exponer a través de una herramienta de modelado personalizada) y de las tablas y vistas generadas por el modelo para unir las tablas base con las tablas de datos definidas por el usuario. Entonces, cada tipo tendría una tabla base con datos básicos y una tabla de usuarios con campos definidos por el usuario.

Tome un documento como ejemplo: los campos típicos serían el nombre, el tipo, la fecha, el autor, etc. Esto iría en la tabla principal. Luego, los usuarios definirían sus propios tipos de documentos especiales con sus propios campos, como contract_end_date, renewal_clause, blah blah blah. Para ese documento definido por el usuario habría una tabla de documento principal, la tabla xcontract, unida en una clave primaria común (por lo que la clave primaria xcontracts también es extranjera en la clave principal de la tabla principal). Entonces generaría una vista para envolver estas dos tablas. Rendimiento al consultar fue rápido. las reglas comerciales adicionales también pueden integrarse en las vistas. Esto funcionó muy bien para mí.

He tenido experiencia o 1, 3 y 4 y todos terminan desordenados, ya que no está claro cuáles son los datos o realmente complicados con algún tipo de categorización suave para dividir los datos en tipos dinámicos de registro.

Estaría tentado de probar XML, debería poder aplicar esquemas contra los contenidos del xml para verificar el tipeo de datos, etc., lo que ayudará a mantener diferentes conjuntos de datos UDF. En las versiones más nuevas de SQL Server puede indexar en campos XML, lo que debería ayudar en el rendimiento. (ver http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx ) por ejemplo

Incluso si proporciona un usuario que agregue columnas personalizadas, no será necesariamente el caso de que la consulta en esas columnas tenga un buen rendimiento. Hay muchos aspectos que entran en el diseño de consultas que les permiten funcionar bien, el más importante de los cuales es la especificación adecuada de lo que se debe almacenar en primer lugar. Por lo tanto, fundamentalmente, ¿desea permitir que los usuarios creen un esquema sin pensar en las especificaciones y puedan obtener rápidamente información de ese esquema? Si es así, es poco probable que una solución de este tipo se escale bien, especialmente si desea permitir que el usuario realice un análisis numérico de los datos.

Opción 1

OMI este enfoque le da un esquema sin conocimiento de lo que significa el esquema, que es una receta para el desastre y una pesadilla para los diseñadores de informes. Es decir, debe tener los metadatos para saber qué columna almacena qué datos. Si esos metadatos se arruinan, tiene el potencial de manguera de sus datos. Además, hace que sea más fácil poner los datos incorrectos en la columna incorrecta. ("¿Qué? String1 contiene el nombre de los conventos? Pensé que eran las drogas favoritas de Chalie Sheen").

Opción 3,4,5

IMO, los requisitos 2, 3 y 4 eliminan cualquier variación de un EAV. Si necesita consultar, ordenar o hacer cálculos con estos datos, entonces un EAV es el sueño de Cthulhu y la pesadilla de su equipo de desarrollo y DBA. EAV creará un cuello de botella en términos de rendimiento y no le dará la integridad de datos que necesita para obtener rápidamente la información que desea. Las consultas se convertirán rápidamente en nudos gordianos de la tabla cruzada.

Opción 2,6

Eso realmente deja una opción: reunir especificaciones y luego construir el esquema.

Si el cliente desea el mejor rendimiento en los datos que desea almacenar, entonces debe pasar por el proceso de trabajar con un desarrollador para comprender sus necesidades y almacenarlo de la manera más eficiente posible. Todavía podría almacenarse en una tabla separada del resto de las tablas con código que construye dinámicamente un formulario basado en el esquema de la tabla. Si tiene una base de datos que permite propiedades extendidas en columnas, incluso podría usarlas para ayudar al generador de formularios a usar etiquetas agradables, información sobre herramientas, etc. de modo que todo lo que sea necesario sea agregar el esquema. De cualquier manera, para construir y ejecutar informes de manera eficiente, los datos deben almacenarse adecuadamente. Si los datos en cuestión tendrán muchos nulos, algunas bases de datos tienen la capacidad de almacenar ese tipo de información. Por ejemplo, SQL Server 2008 tiene una característica llamada Sparse Columns específicamente para datos con muchos nulos.

Si esto fuera solo una bolsa de datos en los que no se hiciera ningún análisis, filtrado o clasificación, diría que una variación de un EAV podría ser el truco. Sin embargo, dados sus requisitos, la solución más eficiente será obtener las especificaciones adecuadas incluso si almacena estas columnas nuevas en tablas separadas y crea formularios dinámicamente fuera de esas tablas.

Columnas dispersas

Nuestra base de datos impulsa una aplicación SaaS (software de asistencia técnica) donde los usuarios tienen más de 7k "campos personalizados". Usamos un enfoque combinado:

  1. (EntityID, FieldID, Value) para buscar los datos
  2. un campo JSON en la tabla de entities , que contiene todos los valores de entidad, usados ​​para mostrar los datos. (De esta forma, no necesita un millón de JOIN para obtener los valores de los valores).

Podrías dividir aún más el # 1 para tener una "tabla por tipo de datos" como sugiere esta respuesta , de esta manera incluso puedes indexar tus UDF.

PD Un par de palabras para defender el enfoque de "Entidad-Valor-Atributo" que todos siguen criticando. Hemos usado # 1 sin # 2 durante décadas y funcionó bien. A veces es una decisión comercial. ¿Tiene tiempo para reescribir su aplicación y rediseñar el DB o puede hacerlo a través de un par de dólares en servidores en la nube, que son realmente baratos en estos días? Por cierto, cuando utilizamos el enfoque n. ° 1, nuestra base de datos tenía millones de entidades a las que accedían cientos de miles de usuarios, y un servidor db de doble núcleo de 16 GB funcionaba muy bien (realmente una vm "r3" en AWS) .

Probablemente crearía una tabla de la siguiente estructura:

  • varchar Nombre
  • varchar Type
  • decimal NumberValue
  • varchar StringValue
  • date DateValue

Los tipos exactos de curso dependen de sus necesidades (y, por supuesto, de los dbms que está utilizando). También podría usar el campo NumberValue (decimal) para int y booleans. Es posible que necesite otros tipos también.

Necesitas algún enlace a los registros maestros que poseen el valor. Probablemente sea más fácil y rápido crear una tabla de campos de usuario para cada tabla maestra y agregar una clave externa simple. De esta forma, puede filtrar registros maestros por campos de usuario de manera fácil y rápida.

Es posible que desee tener algún tipo de información de metadatos. Entonces terminas con lo siguiente:

Tabla UdfMetaData

  • ID int
  • varchar Nombre
  • varchar Type

Tabla MasterUdfValues

  • int Master_FK
  • int MetaData_FK
  • decimal NumberValue
  • varchar StringValue
  • date DateValue

Hagas lo que hagas, no cambiaría dinámicamente la estructura de la tabla. Es una pesadilla de mantenimiento. Tampoco usaría estructuras XML, son demasiado lentas.

SharePoint usa la opción 1 y tiene un rendimiento razonable.

Si está utilizando SQL Server, no pase por alto el tipo sqlvariant. Es bastante rápido y debería hacer tu trabajo. Otras bases de datos pueden tener algo similar.

Los tipos de datos XML no son tan buenos por razones de rendimiento. Si estás haciendo cálculos en el servidor, entonces tienes que deserializarlos constantemente.

La opción 1 suena mal y parece cruddy, pero en cuanto a rendimiento puede ser tu mejor opción. He creado tablas con columnas llamadas Field00-Field99 antes porque no se puede superar el rendimiento. Es posible que también deba considerar su desempeño de INSERT, en cuyo caso este también es el indicado. ¡Siempre puedes crear vistas en esta tabla si quieres que se vea limpio!

Si el rendimiento es la principal preocupación, iría con # 6 ... una tabla por UDF (realmente, esta es una variante de # 2). Esta respuesta está específicamente diseñada para esta situación y la descripción de la distribución de datos y los patrones de acceso descritos.

Pros:

  1. Debido a que indica que algunas UDF tienen valores para una pequeña porción del conjunto de datos en general, una tabla separada le daría el mejor rendimiento porque esa tabla solo será tan grande como debe ser para admitir la UDF. Lo mismo es cierto para los índices relacionados.

  2. También obtiene un impulso de velocidad al limitar la cantidad de datos que deben procesarse para agregaciones u otras transformaciones. Dividir los datos en múltiples tablas le permite realizar algunos de los análisis agregados y otros estadísticos en los datos UDF, luego unir ese resultado a la tabla maestra a través de una clave externa para obtener los atributos no agregados.

  3. Puede usar nombres de tabla / columna que reflejen en realidad los datos.

  4. Usted tiene control completo para usar tipos de datos, restricciones de verificación, valores predeterminados, etc. para definir los dominios de datos. No subestimes el impacto en el rendimiento resultante de la conversión de tipo de datos sobre la marcha. Estas restricciones también ayudan a los optimizadores de consultas RDBMS a desarrollar planes más efectivos.

  5. Si alguna vez necesita usar claves externas, la integridad referencial declarativa incorporada rara vez se supera por la imposición de restricciones basada en el disparador o la aplicación.

Contras:

  1. Esto podría crear muchas tablas. La aplicación de la separación de esquema y / o una convención de nomenclatura aliviaría esto.

  2. Se necesita más código de aplicación para operar la definición y administración de UDF. Espero que este código sea aún menos necesario que para las opciones originales 1, 3 y 4.

Otras Consideraciones:

  1. Si hay algo acerca de la naturaleza de los datos que tendría sentido para agrupar las UDF, debería alentarse. De esta manera, esos elementos de datos se pueden combinar en una sola tabla. Por ejemplo, digamos que tiene UDF para color, tamaño y costo. La tendencia en los datos es que la mayoría de las instancias de estos datos se parecen a

    ''red'', ''large'', 45.03

    más bien que

    NULL, ''medium'', NULL

    En tal caso, no incurrirá en una penalización de velocidad notable combinando las 3 columnas en 1 tabla porque pocos valores serían NULL y evitará hacer 2 tablas más, que son 2 combinaciones menos necesarias cuando necesita acceder a las 3 columnas .

  2. Si llega a un muro de rendimiento de una UDF que está muy poblada y se usa con frecuencia, entonces se debe considerar su inclusión en la tabla maestra.

  3. El diseño de tablas lógicas puede llevarlo a un cierto punto, pero cuando los recuentos de registros son verdaderamente masivos, también debe comenzar a buscar las opciones de partición de tablas provistas por su RDBMS de elección.