Я хочу создать таблицу метаданных пациента/образца, используя подход с атрибутом-атрибутом (EAV).
Вопрос. Как мне обрабатывать переменный тип столбца значения (например, строковый, числовой или внешний ключ в таблице словаря) на основе атрибута?
Примечание: Я не спрашиваю, следует ли использовать подход EAV. Я посмотрел на другие вопросы SO и ссылки и считаем, что это лучший подход для моего использования (например, я не хочу создавать отдельный столбец или стол для каждого атрибута, который может насчитывать сотни). Тем не менее, я, конечно же, пересмотрю другие проекты, учитывая исчерпывающий пример.
Репрезентативные данные
Пациент/образец (сущность) может иметь несколько атрибутов метаданных (например, местоположение лаборатории, выживаемость, тип опухоли), каждый из которых имеет другой тип значения (например, VARCHAR, NUMBER, FOREIGN_KEY *, соответственно).
* FOREIGN_KEY означает, что этот тип значения представляет собой идентификатор внешнего ключа (INTEGER) для таблицы значений словаря (например, список из 10 возможных типов опухолей). Поэтому местоположение лаборатории может быть VARCHAR, так как я не забочусь о нормализации этих значений. Но тип опухоли должен иметь некоторую степень валидации.
Моя таблица может выглядеть примерно так:
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 ???
);
Мне кажется, нужен столбец определения типа значения (attribute_value_type) в таблице metadata_attributes, чтобы узнать, какой столбец/таблицу следует искать.
Возможные подходы
Вот два возможных подхода, о которых я могу думать.
Подход 1: Отдельная таблица EAV с несколькими столбцами
Создайте три разных столбца в таблице patient_metadata - по одному для каждого типа значений.
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)
);
Подход 2: несколько таблиц EAV
Создайте три разные таблицы patient_metadata - по одному для каждого типа значений.
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
);
Другие подходы?
Существуют ли другие подходы?
Короче говоря, я хочу как можно больше уважать реляционную целостность и позволять базе данных знать тип значения, чтобы он мог выполнять базовую проверку. Тем не менее, я считаю, что оба вышеупомянутых подхода потребуют некоторых проверок проверки целостности вручную (для подхода 1 требуется проверка того, что заполняется только один столбец attribute_value и т.д.).
Типы запросов, которые я буду выполнять, будут типичными (например, получить список значений для данного атрибута метаданных, получить список значений для данного пациента (объекта) и атрибута метаданных и т.д.). Я считаю, что в большинстве случаев мне нужно будет запросить тип значения, чтобы узнать, какой столбец или таблица запрашивать. Любой другой путь вокруг этого?
Каковы плюсы и минусы для всех подходов (производительность, структура запросов и т.д.)?
Первый плакат, поэтому заранее заблаговременно и, пожалуйста, не стесняйтесь комментировать форматирование или дальнейшее разъяснение!
