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

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

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

Nota: No estoy preguntando si usar o no un enfoque EAV. He mirado otras preguntas SO y referencias y creo que esta es la mejor enfoque para mi caso de uso (por ejemplo, no quiero crear una columna o tabla separada para cada atributo - que puede numerar en cientos). Sin embargo, sin duda 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 del laboratorio, supervivencia, tipo de tumor) cada uno con un valor tipo (por ejemplo.VARCHAR, NUMBER, FOREIGN_KEY*, respectivamente).

*FOREIGN_KEY significa que este valor tipo es un ID de clave externa (INTEGER) a una tabla de diccionario de valores (por ejemplo, una lista de los 10 posibles tipos de tumores). Así que 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 tabla 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 un value type identifying column (attribute_value_type) en el metadata_attributes table para saber a qué columna / tabla mirar.

Posibles enfoques

Aquí hay dos enfoques posibles que se me ocurren.

Enfoque 1: Tabla EAV única con múltiples columnas

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

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

Crear tres tablas patient_metadata diferentes-una para cada valor tipo.

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 value para que pueda realizar una validación básica. Sin embargo, creo que ambos enfoques anteriores requerirán algún tipo de comprobación manual de integridad (el enfoque 1 requiere una comprobación de que solo se rellene una columna attribute_value, etc.).).

Los tipos de consultas que se llevará a cabo será típico (por ejemplo, recuperar una lista de valores para un determinado atributo de metadatos , recuperar una lista de valores para un determinado paciente (entidad) y metadatos atributo, etc.). Creo que necesitaré consultar el tipo value en la mayoría de los casos para saber qué columna o tabla consultar. Cualquier otra forma de evitar esto?

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

Cartel por primera vez, así que gracias de antemano y por favor no dude en comentar sobre el formato o más aclaraciones!