sql - una - Pares de valores clave en la base de datos relacional

para que sirven las bases de datos relacionales (18)

Antes de continuar con su enfoque, le sugiero humildemente que retroceda y considere si realmente desea almacenar estos datos en una tabla de "pares clave-valor". No conozco su aplicación pero mi experiencia ha demostrado que cada vez que he hecho lo que está haciendo, más tarde desearía haber creado una tabla de colores, una tabla de tela y una tabla de medidas.

Piense en las restricciones de integridad referencial, si toma el enfoque de par clave-valor, la base de datos no puede decirle cuándo está tratando de almacenar una identificación de color en un campo de tamaño

Piense en los beneficios de rendimiento de unirse a una tabla con 10 valores en comparación con un valor genérico que puede tener miles de valores en múltiples dominios. ¿Qué tan útil es realmente un índice sobre Key Value?

Por lo general, el razonamiento detrás de hacer lo que está haciendo es porque los dominios deben ser "definibles por el usuario". Si ese es el caso, incluso yo no te empujaré a crear tablas sobre la marcha (aunque ese es un enfoque viable).

Sin embargo, si su razonamiento se debe a que cree que será más fácil de administrar que varias tablas, o porque está visualizando una interfaz de usuario de mantenimiento que es genérica para todos los dominios, entonces deténgase y piense detenidamente antes de continuar.

¿Alguien tiene experiencia con el almacenamiento de pares clave-valor en una base de datos?

He estado usando este tipo de tabla:

CREATE TABLE key_value_pairs ( itemid varchar(32) NOT NULL, itemkey varchar(32) NOT NULL, itemvalue varchar(32) NOT NULL, CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey) )

Entonces, por ejemplo, las siguientes filas podrían existir:

itemid itemkey itemvalue ---------------- ------------- ------------ 123 Colour Red 123 Size Medium 123 Fabric Cotton

El problema con este esquema es que la sintaxis SQL requerida para extraer datos es bastante compleja. ¿Sería mejor crear una serie de columnas clave / valor?

CREATE TABLE key_value_pairs ( itemid varchar(32) NOT NULL, itemkey1 varchar(32) NOT NULL, itemvalue1 varchar(32) NOT NULL, itemkey2 varchar(32) NOT NULL, itemvalue2 varchar(32) NOT NULL, . . .etc . . . )

Esto será más fácil y rápido de consultar pero carece de la extensibilidad del primer enfoque. ¿Algún consejo?

Creo que está haciendo lo correcto, siempre que las claves / valores para un tipo determinado de elemento cambien con frecuencia.
Si son bastante estáticos, entonces simplemente hacer más amplia la tabla de elementos tiene más sentido.

Usamos un enfoque similar (pero bastante más complejo), con mucha lógica alrededor de las claves / valores, así como tablas para los tipos de valores permitidos para cada clave.
Esto nos permite definir elementos como simplemente otra instancia de una clave, y nuestra tabla central asigna tipos de clave arbitraria a otros tipos de claves arbitrarias. Puede atar tu cerebro rápidamente en nudos, pero una vez que hayas escrito y encapsulado la lógica para manejarlo todo, tienes mucha flexibilidad.

Puedo escribir más detalles de lo que hacemos si es necesario.

Creo que la mejor manera de diseñar tales tablas es la siguiente:

Haga los campos frecuentemente utilizados como columnas en la base de datos.
Proporcione una columna Misc que contenga un diccionario (en JSON / XML / otra cadena formeat) que contendrá los campos como pares clave-valor.

Puntos salientes:

Puede escribir sus consultas SQL normales para consultar SQL en la mayoría de las situaciones.
Puede hacer un FullTextSearch en los pares clave-valor. MySQL tiene un motor de búsqueda de texto completo, de lo contrario puede usar consultas "me gusta" que son un poco más lentas. Si bien la búsqueda de texto completo es mala, suponemos que esas consultas son menos, por lo que no deberían causar demasiados problemas.
Si sus pares clave-valor son indicadores booleanos simples, esta técnica tiene el mismo poder que tener una columna separada para la clave. Cualquier operación más compleja en los pares de valores clave debe realizarse fuera de la base de datos.
Ver la frecuencia de las consultas durante un período de tiempo le indicará qué pares clave-valor deben convertirse en columnas.
Esta técnica también hace que sea fácil forzar restricciones de integridad en la base de datos.
Proporciona una ruta más natural para que los desarrolladores vuelvan a factorizar su esquema y código.

El primer método es mucho más flexible al costo que mencionas.

Y el segundo enfoque nunca es viable como demostraste. En cambio, lo harías (según tu primer ejemplo)

create table item_config (item_id int, colour varchar, size varchar, fabric varchar)

por supuesto, esto solo funcionará cuando se conozca la cantidad de datos y no cambie mucho.

Como regla general, cualquier aplicación que requiera cambiar el DDL de tablas para hacer un trabajo normal debería recibir una segunda y tercera reflexión.

En la mayoría de los casos en que usarías el primer método, es porque realmente no te has sentado y pensado en tu modelo ... "Bueno, todavía no sabemos cuáles serán las llaves". En general, este es un diseño bastante pobre. Va a ser más lento que tener tus llaves como columnas, lo cual debería ser.

También me pregunto por qué tu identificación es varchar.

En el raro caso de que realmente deba implementar una tabla de clave / valor, la primera solución está bien, aunque, en general, quisiera tener las claves en una tabla separada, por lo que no está almacenando variables como las claves en su clave / tabla de valores

p.ej,

CREATE TABLE valid_keys ( id NUMBER(10) NOT NULL, description varchar(32) NOT NULL, CONSTRAINT pk_valid_keys PRIMARY KEY(id) ); CREATE TABLE item_values ( item_id NUMBER(10) NOT NULL, key_id NUMBER(10) NOT NULL, item_value VARCHAR2(32) NOT NULL, CONSTRAINT pk_item_values PRIMARY KEY(id), CONSTRAINT fk_item_values_iv FOREIGN KEY (key_id) REFERENCES valid_keys (id) );

Entonces, incluso puede volverse loco y agregar un "TIPO" a las teclas, lo que permite verificar algunos tipos.

Hay otra solución que se encuentra en algún lugar entre los dos. Puede usar una columna de tipo xml para las claves y valores. Así que mantienes el campo itemid, luego tienes un campo xml que contiene el xml definido para algunos pares de valores clave como <items> <item key="colour" value="red"/><item key="xxx" value="blah"/></items> Luego, cuando extraes tus datos de la base de datos, puedes procesar el xml de diferentes maneras. Dependiendo de tu uso Esta es una solución extensible.

La segunda mesa está muy des-normalizada. Me quedaría con el primer enfoque.

La violación de las reglas de normalización está bien siempre y cuando el requisito comercial aún pueda cumplirse. Tener key_1, value_1, key_2, value_2, ... key_n, value_n puede estar bien, hasta el punto que necesita key_n+1, value_n+1 .

Mi solución ha sido una tabla de datos para atributos compartidos y XML para atributos únicos. Eso significa que uso ambos. Si todo (o la mayoría de las cosas) tiene un tamaño, entonces el tamaño es una columna en la tabla. Si solo el objeto A tiene el atributo Z, entonces Z se almacena como XML, como la respuesta de Peter Marshall ya dada.

Los tiempos han cambiado. Ahora tiene otros tipos de bases de datos que puede usar al lado de las bases de datos relacionales. Las opciones de NOSQL ahora incluyen, Almacenes de columna, Almacenes de documentos, Gráfico y Multi-modelo (Ver: http://en.wikipedia.org/wiki/NoSQL ).

Para las bases de datos de Key-Value, sus opciones incluyen (pero no se limitan a) CouchDb, Redis y MongoDB.

No entiendo por qué el SQL para extraer datos debe ser complejo para su primer diseño. Sin duda, para obtener todos los valores de un artículo, simplemente haz esto:

SELECT itemkey,itemvalue FROM key_value_pairs WHERE itemid=''123'';

o si solo quieres una clave en particular para ese artículo:

SELECT itemvalue FROM key_value_pairs WHERE itemid=''123'' AND itemkey=''Fabric'';

El primer diseño también le brinda la flexibilidad de agregar fácilmente nuevas claves cuando lo desee.

Por experiencia, he descubierto que ciertas claves serán más ampliamente utilizadas o consultadas más a menudo. Por lo general, hemos desnormalizado ligeramente el diseño para incluir un campo específico en la tabla principal de "elementos".

p.ej. si cada elemento tiene un color, puede agregar la columna Color a su tabla de elementos. La tela y el tamaño se pueden usar con menos frecuencia y se pueden mantener separados en la tabla de pares clave-valor. Incluso puede mantener el color en la tabla de pares clave-valor, pero duplicar los datos en la tabla de elementos para obtener los beneficios de rendimiento.

Obviamente, esto varía según los datos y la flexibilidad que necesita para que los pares clave-valor sean. También puede hacer que los datos de tus atributos no se ubiquen de manera constante. Sin embargo, la desnormalización simplifica en gran medida las consultas y mejora su rendimiento también.

Por lo general, solo consideraría la desnormalización cuando el rendimiento se convierta en un problema, no solo para simplificar una consulta.

PostgreSQL 8.4 admite el tipo de datos hstore para almacenar conjuntos de pares (clave, valor) dentro de un único campo de datos PostgreSQL. Consulte http://www.postgresql.org/docs/8.4/static/hstore.html para conocer su información de uso. Aunque es una pregunta muy antigua, pero pensé pasar esta información pensando que podría ayudar a alguien.

Si las claves son dinámicas, o hay muchas, utilice la tabla de asignación que tiene como primer ejemplo. Además, esta es la solución más general, se adapta mejor en el futuro a medida que agrega más claves, es fácil codificar el SQL para obtener los datos, y la base de datos podrá optimizar la consulta mejor de lo que imagina ( es decir, no me esforzaría en optimizar prematuramente este caso a menos que se pruebe que es un cuello de botella en las pruebas posteriores, en cuyo caso podría considerar las siguientes dos opciones a continuación).

Si las claves son un conjunto conocido, y no hay muchas (<10, quizás <5), entonces no veo el problema de tenerlas como columnas de valor en el elemento.

Si hay un número medio de claves fijas conocidas (10 - 30), entonces tal vez tenga otra tabla para contener los detalles del elemento.

Sin embargo, nunca veo la necesidad de utilizar su segunda estructura de ejemplo, parece engorroso.

Si tiene muy pocas claves posibles, entonces las almacenaría como columnas. Pero si el conjunto de claves posibles es grande, entonces su primer enfoque es bueno (y el segundo enfoque sería imposible).

¿O es así que cada elemento solo puede tener un número finito de claves, pero las claves podrían ser algo de un conjunto grande?

También podría considerar usar un Object Relational Mapper para facilitar las consultas.

Si va por la ruta de una tabla KVP, y debo decir que no me gusta esa técnica en absoluto, ya que es realmente difícil de consultar, entonces debería considerar agrupar los valores para una ID de artículo individual utilizando una técnica apropiada para cualquier plataforma en la que estés.

Los RDBMS tienen una tendencia a dispersar filas para evitar la contención de bloques en las inserciones y si tiene 8 renglones para recuperar, puede encontrar fácilmente acceso a 8 bloques de la tabla para leerlos. En Oracle haría bien en considerar un clúster de hash para almacenarlos, lo que mejoraría enormemente el rendimiento al acceder a los valores para una identificación de artículo determinada.

Su ejemplo no es un muy buen ejemplo del uso de pares de valores clave. Un mejor ejemplo sería el uso de algo así como una tabla de tarifas, una tabla de clientes y una tabla de sugerencias de clientes en una aplicación de facturación. La tabla de tarifas constaría de campos como: fee_id, fee_name, fee_description La tabla Customer_Fee constaría de campos como: customer_id, fee_id, fee_value

Una vez usé pares clave-valor en una base de datos con el propósito de crear una hoja de cálculo (utilizada para el ingreso de datos) en la cual un cajero resumía su actividad de trabajar en un cajón de efectivo. Cada par de k / v representaba una celda nombrada en la que el usuario ingresaba una cantidad monetaria. La razón principal de este enfoque es que la hoja de cálculo estaba muy sujeta a cambios. Se agregaron nuevos productos y servicios de forma rutinaria (así aparecieron nuevas células). Además, ciertas células no eran necesarias en ciertas situaciones y podían descartarse.

La aplicación que escribí fue una reescritura de una aplicación que rompió la hoja del cajero en secciones separadas, cada una representada en una tabla diferente. El problema aquí era que a medida que se agregaban productos y servicios, se requerían modificaciones de esquema. Al igual que con todas las opciones de diseño, existen ventajas y desventajas para tomar una determinada dirección en comparación con otra. Mi rediseño sin duda se hizo más lento y consumió más rápidamente el espacio en disco; sin embargo, fue muy ágil y permitió agregar nuevos productos y servicios en minutos. Sin embargo, la única cuestión que se debe tener en cuenta es el consumo de disco; no había otros dolores de cabeza que pueda recordar.

Como ya se mencionó, la razón por la que generalmente considero un enfoque de pares clave-valor es cuando los usuarios -este podría ser el propietario de una empresa- quieren crear sus propios tipos con un conjunto de atributos específico del usuario. En tales situaciones, he llegado a la siguiente determinación.

Si no hay necesidad de recuperar los datos por estos atributos o la búsqueda puede diferirse a la aplicación una vez que se ha recuperado un trozo de datos, recomiendo almacenar todos los atributos en un solo campo de texto (usando JSON, YAML, XML, etc. ) Si hay una gran necesidad de recuperar datos por estos atributos, se vuelve complicado.

Puede crear una sola tabla de "atributos" (id, item_id, key, value, data_type, sort_value) donde la columna de ordenamiento codifica el valor real en una representación de cadena ordenable. (por ejemplo, fecha: "2010-12-25 12:00:00", número: "0000000001") O puede crear tablas de atributos por tipo de datos (por ejemplo, string_attributes, date_attributes, number_attributes). Entre los numerosos pros y contras de ambos enfoques: el primero es más simple, el segundo es más rápido. Ambos causarán que escriba consultas complejas y desagradables.

el primer método está bastante bien. puede crear una UDF que extraiga los datos deseados y simplemente llame a eso.