database - una - modelo de base de datos relacional ejemplo

¿Cómo puedo manejar diferentes tipos de datos en un diseño Entidad-Atributo-Valor(por ejemplo, una sola tabla con varias columnas o varias tablas por tipo de datos)?

Question

Quiero crear una tabla de metadatos de muestra / paciente utilizando un enfoque entidad-atributo-valor (EAV).

Pregunta : ¿Cómo debo manejar el tipo de columna variable del valor (por ejemplo, cadena, clave numérica o clave externa a la tabla del diccionario) según el atributo ?

Nota: No estoy preguntando si usar o no un enfoque de EAV . He examinado otras preguntas y references SO y creo que este es el mejor enfoque para mi caso de uso (por ejemplo, no quiero crear una columna o tabla separada para cada atributo , que puede llegar a cientos). Sin embargo, ciertamente reconsideraré otros diseños dado un ejemplo completo.

Datos representativos

Un paciente / muestra ( entidad ) puede tener múltiples atributos de metadatos (por ejemplo, ubicación de laboratorio, supervivencia, tipo de tumor) cada uno con un tipo de valor diferente (por ejemplo, VARCHAR , NUMBER , FOREIGN_KEY *, respectivamente).

* FOREIGN_KEY significa que este tipo de valor es un ID de clave foránea ( INTEGER ) a una tabla de valores del diccionario (por ejemplo, una lista de los 10 tipos posibles de tumores). Por lo tanto, la ubicación del laboratorio puede ser VARCHAR ya que no me importa normalizar esos valores. Pero el tipo de tumor debe tener algún grado de validación.

El diseño de mi mesa puede verse algo como esto:

CREATE TABLE patients (
  patient_id INTEGER CONSTRAINT pk_patients PRIMARY KEY,
  patient_name VARCHAR2(50) NOT NULL
);

CREATE TABLE metadata_attributes (
  attribute_id INTEGER CONSTRAINT pk_metadata_attributes PRIMARY KEY,
  attribute_name VARCHAR2(50) NOT NULL,
  attribute_value_type VARCHAR(50) NOT NULL -- e.g. VARCHAR, NUMBER, or ID
);

CREATE TABLE patient_metadata (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value ???
);

Creo que necesito una columna de identificación de tipo de valor (attribute_value_type) en la tabla metadata_attributes para saber a qué columna / tabla mirar.

Posibles enfoques

Aquí hay dos posibles enfoques que se me ocurren.

Enfoque 1: tabla única de EAV con varias columnas

Cree tres columnas diferentes en la tabla patient_metadata, una para cada tipo de valor .

CREATE TABLE patient_metadata (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_varchar_value VARCHAR(50),
  attribute_number_value NUMBER,
  attribute_id_value CONSTRAINT fk_pm_values REFERENCES some_table_of_values(value_id)
);

Enfoque 2: Múltiples tablas EAV

Cree tres tablas de datos de pacientes diferentes, una para cada tipo de valor .

CREATE TABLE patient_metadata_varchar (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value VARCHAR(50) NOT NULL
);

CREATE TABLE patient_metadata_number (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value NUMBER NOT NULL
);

CREATE TABLE patient_metadata_id (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value CONSTRAINT fk_pm_values REFERENCES some_table_of_values(value_id) NOT NULL
);

¿Otros enfoques?

¿Hay otros enfoques por ahí?

En resumen, quiero respetar la integridad relacional tanto como sea posible y permitir que la base de datos conozca el tipo de valor para que pueda realizar la validación básica. Sin embargo, creo que los dos enfoques anteriores requerirán algún tipo de comprobación de integridad manual (el enfoque 1 requiere una comprobación de que solo se rellena una columna de valor-atributo, etc.).

Los tipos de consultas que realizaré serán típicos (por ejemplo, recuperar una lista de valores para un atributo de metadatos dado, recuperar una lista de valores para un paciente determinado ( entidad ) y un atributo de metadatos, etc.). Creo que necesitaré consultar el tipo de valor en la mayoría de los casos para saber qué columna o tabla consultar. ¿Alguna otra forma de evitar esto?

¿Cuáles son los pros y los contras de todos los enfoques (rendimiento, estructura de consulta, etc.)?

Póster por primera vez, así que gracias de antemano y no dude en comentar sobre el formato o más aclaraciones.

Answer 1

Este es un problema bien conocido. El problema con el enfoque que mencionó es que debe conocer el tipo de atributo antes de realizar la consulta. No es el fin del mundo porque administras metadatos pero aún así ...

Dos posibles soluciones podrían ser

1. utilizando un tipo de datos varchar2 para representar todos los tipos de datos en un formato conocido. Los números y caracteres no son un problema, los valores de fecha se pueden escribir de una manera predefinida (es como implementar to_String() en cualquier diseño OO).
2. Utilice el tipo de datos ANYDATA . Yo personalmente jugué un poco con él pero decidí no usarlo.

Answer 2

Lo más fácil, el más eficaz, etc. es convertir todos los valores en la base de datos a cadenas. Los problemas como los indicados generalmente serán obvios, e incluso las columnas bien escritas sufren exactamente el mismo tipo de problemas, que generalmente se expresan como problemas de rendimiento.

Con un poco de cuidado, puede mantener el orden de intercalación, si eso importa (p. Ej., Al formatear las fechas como año / mes / día), y la base de datos no debe realizar la validación de los tipos, ya que es demasiado tarde. Los números negativos son una molestia, al igual que los flotadores, pero es altamente inusual indexar por un número que puede ser negativo o flotante, y las clasificaciones en memoria son generalmente rápidas.

Cuando el tipo de datos no sea obvio, o deba ser conocido por un procesador descendente, agregue una columna de tipo.

En general, todas las restricciones de integridad con respecto a los valores de columna se pueden verificar antes de escribir el registro, ya sea en código (bueno) o en disparadores (no tan bueno). Tratar de usar las características nativas con diferentes tipos solo le llevará hasta el momento, y probablemente no sea tan útil de todos modos, ya que los valores a menudo tienen muchas restricciones específicas de negocios de todos modos, por ejemplo, la fecha de nacimiento no debe ser nula y después de 1900.

Para el rendimiento, use índices compuestos que incluyan la entidad y el atributo como prefijos. Los índices pueden dividirse por el prefijo entidad-atributo, reduciendo cualquier impacto de la profundidad adicional del índice, y se comprimen realmente bien (el prefijo se comprimirá a uno o dos bytes), por lo que la diferencia de tamaño es mínima.

La consulta desde las tablas de EAV a menudo se realiza mejor en vistas que desempaquetarán las entidades para que la estructura pueda devolverse a algo que usted esperaría, aunque esto puede ser irrelevante si se trata de columnas variables, por ejemplo, en formas de pacientes que se caracterizan Por un gran número de elementos variados en función de la historia. Entonces, probablemente sea más fácil de procesar en su lógica empresarial.

Finalmente, en la actualidad este tipo de datos simplemente no se almacena en un estilo de base de datos relacional orientado a columnas. Generalmente se almacena como un documento XML (o JSON) (tipos de XML en Oracle), y la mayoría de las bases de datos proporcionan alguna capacidad de procesamiento XML nativo para buscar y manipular dichos datos. Esto está bien para el almacenamiento y recuperación de formularios normales, pero tiende a hacer consultas arbitrarias como "darme a todos los pacientes mayores de 60 años que han tenido neumonía en el último año" bastante lento, o un poco más complicado, ya que se necesita una indexación inversa etiquetada. Sin embargo, vale la pena ver si un enfoque orientado a documentos / texto es una solución mejor.

¡Buena suerte!