sql - restricciones - Pares clave/valor en una tabla de base de datos

restricciones base de datos sql

Question

Necesito diseñar una tabla de clave / valor en mi base de datos y estoy buscando orientación sobre la mejor manera de hacerlo. Básicamente, necesito poder asociar valores a un conjunto dinámico de propiedades con nombre y aplicarlas a una clave externa.

Las operaciones que necesito para poder apoyar son:

• Aplicar un par de clave / valor a un grupo de elementos
• Enumerar todas las claves actualmente activas
• Determine todos los artículos que tienen un valor para una clave dada
• Determine todos los elementos donde el valor asociado con una clave dada concuerda con algunos criterios.

Parece que la forma más sencilla de hacerlo es definir una tabla:

CREATE TABLE KeyValue (
  id    int,
  Key   varchar...,
  Value varchar...
);

Parece que es probable que esté duplicando una gran cantidad de datos en la columna Clave porque es probable que una determinada clave se defina para una gran cantidad de documentos. Reemplazar la clave varchar con una búsqueda de enteros en otra tabla parece aliviar este problema (y hacer que sea significativamente más eficiente enumerar todas las claves activas), pero me limita el problema de mantener esa tabla de búsqueda (insistiendo en ella cada vez que quiero) para definir una propiedad y potencialmente eliminar la entrada cada vez que se borre una clave / valor).

¿Cuál es la mejor manera de hacer esto?

Answer 1

¡Crea vistas actualizables! . También mira esto para ver un ejemplo.

Answer 2

Me parece que podría tener un par de opciones de diseño.

Opción 1: un diseño de dos mesas que insinuó en su respuesta

Keys (
 id int not null auto_increment
 key string/int
)
values (
 id int not null auto_increment
 key_id int
 value string/varchar/int
)

Opción 2: tal vez como sambo99 señaló que podría modificar esto:

keys (
 id int not null auto_increment
 key string/int
 hash_code int -- this would be computed by the inserting code, so that lookups would effectively have the id, and you can look them up directly
)

values (
 id int not null auto_increment -- this column might be nice since your hash_codes might colide, and this will make deletes/updates easier
 key_id int -- this column becomes optional
 hash_code int
 value string/varchar/int...
)

-

Answer 3

Una opción que puede valer la pena explorar es digerir la clave con SHA1 o MD5 antes de insertarla en la tabla.

Eso le permitirá deshacerse de la tabla de búsqueda, pero no podrá iterar a través de las teclas porque solo va en una dirección.

Answer 4

No optimices esto a menos que tengas que hacerlo. ¿Cuál es la longitud promedio de una clave? ¿Esta mesa será tan grande que no cabe en la memoria de su servidor si la implementa de forma ingenua? Sugeriría implementarlo de la manera más simple, medir el rendimiento y luego volver a implementar solo si el rendimiento es un problema.

Si el rendimiento es un problema, entonces el uso de una clave entera y una tabla separada es probablemente el camino a seguir (las UNIONES en las columnas enteras suelen ser más rápidas que las UNIONES usando columnas de longitud variable). Pero la primera regla de optimización es MEDIR PRIMERO: asegúrese de que su código supuestamente optimizado haga que la cosa se ejecute más rápido.

Answer 5

Está empleando un modelo de base de datos llamado Entity-Attribute-Value . Esta es una forma común de almacenar pares clave / valor en una base de datos relacional, pero tiene una serie de debilidades con respecto a la normalización y eficiencia de la base de datos.

Sí, el diseño de la mesa que mostró es la forma más común de hacerlo. En este diseño, cada atributo de cada entidad obtiene una fila distinta en su tabla de KeyValue .

Aplique un par de clave / valor a un grupo de elementos: debe agregar una fila para cada elemento del grupo.

INSERT INTO KeyValue (id, key, value) VALUES (101, ''color'', ''green'');
INSERT INTO KeyValue (id, key, value) VALUES (102, ''color'', ''green'');
INSERT INTO KeyValue (id, key, value) VALUES (103, ''color'', ''green'');

También puede preparar la instrucción INSERT con parámetros y ejecutar una serie de identificadores de elementos en un bucle, o lo que sea.

Enumerar todas las claves actualmente activas:

SELECT DISTINCT Key FROM KeyValue;

Determine todos los elementos que tienen un valor para una clave determinada:

SELECT id FROM KeyValue WHERE Key = ''color'';

Determine todos los elementos en los que el valor asociado con una clave determinada concuerde con algunos criterios:

SELECT id FROM KeyValue WHERE Value = ''green'';

Algunos de los problemas con Entity-Attribute-Value son:

• No hay forma de asegurarse de que las claves se deletreen de la misma manera para todos los artículos
• No es posible hacer que algunas claves sean obligatorias para todos los elementos (es decir, NO NULO en un diseño de tabla convencional).
• Todas las claves deben usar VARCHAR para el valor; no puede almacenar diferentes tipos de datos por clave.
• No hay forma de usar integridad referencial; no se puede hacer una CLAVE EXTRAÑA que se aplique a los valores de algunas teclas y otras no.

Básicamente, Entity-Attribute-Value no es un diseño de base de datos normalizado.