Как построить отношения между несколькими подобъектами и атрибутом списка?

Question

Модель приложения (на основе PHP и MySQL), над которым я сейчас работаю, содержит наследование, аналогичное описанному здесь. Упрощенная для целей этого вопроса структура классов может выглядеть так:

Чтобы сопоставить это с базой данных, я использую шаблон проектирования наследования таблиц классов. Вот физическая модель данных:

Наиболее специфические атрибуты на самом деле специфичны для каждого подкласса. Но есть некоторые атрибуты, которые необходимы в нескольких классах (но также и не во всех из них - в противном случае ими можно управлять в Foo класс / таблица). Когда это простой атрибут, он вызывает некоторое дублирование кода, но это не большая проблема. Но есть также несколько случаев со сложными атрибутами.

Например: FooTypeBaz а также FooTypeBuz должен содержать список Whatever элементы.

Обычно я бы это реализовал 1:n отношения со столом whatever содержащий FOREIGN KEY, Но в этом случае мне понадобится несколько FOREIGN KEY столбцы whatever (за foo_type_baz, foo_type_buzи, может быть, еще несколько таблиц). Это грязно.

Другое решение: что-то вроде "фасадного" стола для стола whatever:

Выглядит лучше (для меня), но я все еще не доволен этой моделью.

Как построить отношения между несколькими подобъектами и атрибутом коллекции / списка? Есть ли элегантное решение этой проблемы? Может быть, лучшая практика / шаблон дизайна?

Answer 1

Записать отношения достаточно просто - вы можете составить таблицу foo_whatever (foo_id PK, whatever_set_id FK) и вставлять строки только для соответствующих идентификаторов. Однако эта схема не налагает никаких ограничений на подтипы, которые можно связать с какими-либо наборами, но и ваша существующая схема не навязывает взаимоисключающие подтипы. Возможно применить оба метода в одной и той же технике.

Рассмотрите возможность включения индикатора типа на все foo_* таблицы, например, используя enum('bar', 'baz', 'buz'), Это предоставляет информацию о подтипе в foo (что может быть удобнее, чем объединение трех таблиц для поиска соответствия) и позволяет ограничениям внешнего ключа и проверочным ограничениям применять исключительные подтипы и ограничивать типы, которые могут быть записаны в foo_whatever, Да, это включает в себя немного избыточной информации, но она небольшая и нет риска аномалий обновления.

Использование составных ограничений внешнего ключа, которые включают индикатор типа, вместе с проверочными ограничениями, которые ограничивают значение индикатора типа для каждой таблицы подтипов, должны помочь. Вот схема, которую я предлагаю:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('bar','baz','buz') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_id` (`id`,`type`)
);

CREATE TABLE `foo_type_bar` (
  `foo_id` int(11) NOT NULL,
  `foo_type` enum('bar','baz','buz') NOT NULL CHECK (foo_type = 'bar'),
  PRIMARY KEY (`foo_id`),
  KEY `foo_bar_fk` (`foo_id`,`foo_type`),
  CONSTRAINT `foo_bar_fk` FOREIGN KEY (`foo_id`, `foo_type`)
  REFERENCES `foo` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `foo_type_baz` (
  `foo_id` int(11) NOT NULL,
  `foo_type` enum('bar','baz','buz') NOT NULL CHECK (foo_type = 'baz'),
  PRIMARY KEY (`foo_id`),
  KEY `foo_baz_fk` (`foo_id`,`foo_type`),
  CONSTRAINT `foo_baz_fk` FOREIGN KEY (`foo_id`, `foo_type`)
  REFERENCES `foo` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `foo_type_buz` (
  `foo_id` int(11) NOT NULL,
  `foo_type` enum('bar','baz','buz') NOT NULL CHECK (foo_type = 'buz'),
  PRIMARY KEY (`foo_id`),
  KEY `foo_buz_fk` (`foo_id`,`foo_type`),
  CONSTRAINT `foo_buz_fk` FOREIGN KEY (`foo_id`, `foo_type`)
  REFERENCES `foo` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `foo_whatever` (
  `foo_id` int(11) NOT NULL,
  `foo_type` enum('bar','baz','buz') NOT NULL CHECK (foo_type IN ('baz', 'buz')),
  `whatever_set_id` int(11) NOT NULL,
  PRIMARY KEY (`foo_id`),
  KEY `whatever_foo_fk` (`foo_id`,`foo_type`),
  KEY `whatever_set_fk` (`whatever_set_id`),
  CONSTRAINT `whatever_foo_fk` FOREIGN KEY (`foo_id`, `foo_type`)
  REFERENCES `foo` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `whatever_set_fk` FOREIGN KEY (`whatever_set_id`)
  REFERENCES `whatever_set` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

Однако, поскольку MySQL игнорирует проверочные ограничения, вам потребуется использовать триггеры для достижения того же:

DELIMITER ;;

CREATE TRIGGER foo_bar_insert_type_check
    BEFORE INSERT ON foo_type_bar
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type != 'bar' THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_type_bar';
    END IF; 
END;;

CREATE TRIGGER foo_bar_update_type_check
    BEFORE UPDATE ON foo_type_bar
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type != 'bar' THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_type_bar';
    END IF; 
END;;

CREATE TRIGGER foo_baz_insert_type_check
    BEFORE INSERT ON foo_type_baz
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type != 'baz' THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_type_baz';
    END IF; 
END;;

CREATE TRIGGER foo_baz_update_type_check
    BEFORE UPDATE ON foo_type_baz
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type != 'baz' THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_type_baz';
    END IF; 
END;;

CREATE TRIGGER foo_buz_insert_type_check
    BEFORE INSERT ON foo_type_buz
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type != 'buz' THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_type_buz';
    END IF; 
END;;

CREATE TRIGGER foo_buz_update_type_check
    BEFORE UPDATE ON foo_type_buz
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type != 'buz' THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_type_buz';
    END IF; 
END;;

CREATE TRIGGER foo_whatever_insert_type_check
    BEFORE INSERT ON foo_whatever
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type NOT IN ('baz', 'buz') THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_whatever';
    END IF; 
END;;

CREATE TRIGGER foo_whatever_update_type_check
    BEFORE UPDATE ON foo_whatever
    FOR EACH ROW
BEGIN 
    IF NEW.foo_type NOT IN ('baz', 'buz') THEN 
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'Invalid foo_type in foo_whatever';
    END IF; 
END;;

DELIMITER ;