Как лучше реализовать связь One-To-One в MySQL (Symfony3, Doctrine ORM)?

Question

[Anton Dyshkant]

У меня возникла необходимость работать с созданной 10 лет назад базой данный. Там я обнаружил некоторые вещи, которые мне показались немного странными (если я не прав, поправьте меня пожалуйста).

Предположим, есть сущность MainEntity, которой соответствуют по схеме 1-к-1 разнородные (т.е. различные по своей структуре) сущности AdditionalEntity1, AdditionalEntity2 и AdditionalEntity3. Я ожидал увидеть следующую схему базы данных:

Ожидаемая структура БД

CREATE TABLE `main_entity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `additional_entity_1_id` int(11) NOT NULL,
  `additional_entity_2_id` int(11) NOT NULL,
  `additional_entity_3_id` int(11) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `additional_entity_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `additional_entity_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `additional_entity_3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ALTER TABLE `main_entity`
  ADD KEY `additional_entity_1_id` (`additional_entity_1_id`),
  ADD KEY `additional_entity_2_id` (`additional_entity_2_id`),
  ADD KEY `additional_entity_3_id` (`additional_entity_3_id`);

ALTER TABLE `main_entity`
  ADD CONSTRAINT `main_entity_ibfk_1` FOREIGN KEY (`additional_entity_1_id`) REFERENCES `additional_entity_1` (`id`),
  ADD CONSTRAINT `main_entity_ibfk_2` FOREIGN KEY (`additional_entity_2_id`) REFERENCES `additional_entity_2` (`id`),
  ADD CONSTRAINT `main_entity_ibfk_3` FOREIGN KEY (`additional_entity_3_id`) REFERENCES `additional_entity_3` (`id`);

Однако я увидел вот что:

Фактическая структура БД

CREATE TABLE `main_entity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `additional_entity_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_entity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `additional_entity_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_entity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `additional_entity_3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_entity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ALTER TABLE `additional_entity_1`
  ADD KEY `main_entity_id` (`main_entity_id`),
  ADD UNIQUE KEY `id__main_entity_id` (`id`, `main_entity_id`);

ALTER TABLE `additional_entity_2`
  ADD KEY `main_entity_id` (`main_entity_id`),
  ADD UNIQUE KEY `id__main_entity_id` (`id`, `main_entity_id`);

ALTER TABLE `additional_entity_3`
  ADD KEY `main_entity_id` (`main_entity_id`),
  ADD UNIQUE KEY `id__main_entity_id` (`id`, `main_entity_id`);

ALTER TABLE `additional_entity_1`
  ADD UNIQUE KEY `main_entity_id` (`main_entity_id`);

ALTER TABLE `additional_entity_1`
  ADD CONSTRAINT `additional_entity_1_ibfk_1` FOREIGN KEY (`main_entity_id`) REFERENCES `main_entity` (`id`);

ALTER TABLE `additional_entity_2`
  ADD CONSTRAINT `additional_entity_2_ibfk_1` FOREIGN KEY (`main_entity_id`) REFERENCES `main_entity` (`id`);

ALTER TABLE `additional_entity_3`
  ADD CONSTRAINT `additional_entity_3_ibfk_1` FOREIGN KEY (`main_entity_id`) REFERENCES `main_entity` (`id`);

Фактически на уровне кода получается одно и то же: связь один-к-одному связывает каждую из вспомогательных сущностей с главной.

Однако нельзя не заметить, что на уровне базы данных это две принципиально разные схемы.

Мои вопросы:

1. Чем эти схемы отличаются? Есть ли какие-то "подводные камни" каждой их этих реализаций?
   
2. Какую схему использовать более предпочтительно (в т.ч. с учетом использования Doctrine и Symfony3)?
   

Answer 1

[Vladislav Vlastovskiy]

Можно использовать любую схему.
Я выбрал бы вторую, так как при добавлении/удалении новых типов связи, не надо делать ALTER TABLE основной.
В Doctrine эти связи реализуются одинаково, через One-To-One, с той лишь разнице, у какой сущности будет прописана связь.

Вообще структура БД должна быть логична, так например, должна ли знать MainEntity про AdditionalEntity1, или AdditionalEntity1 должен знать про MainEntity.

Answer 2

[xmoonlight]

Вы привели 2 частных случая, но не привели описание взаимодействия этих сущностей.
Поэтому ответ однозначно дать на данный момент нельзя: мало данных.
1. Не всегда нужна жёсткая связь по ключу в БД
2. Не всегда нужна кросс-связь (все знаю про друг друга)
Всё зависит от бизнес-процесса и задачи выборки данных в этом процессе.

Answer 3

[Ильдар]

1. В первом варианте есть ошибки:
- Если вы получите запись из таблицы additional_entity_N непонятно будет к какому main_entity эта запись относиться, чтобы узнать надо делать дополнительный запрос к main_entity.
- В таблицах additional_entity_N могут существовать записи, которые не относятся ни к одной записи из main_entity.
- Легко запутаться при отладке БД, потому что id во всех 4х таблицах разные.
- Чтобы добавить новый additional_entity_N вам нужно будет создать запись additional_entity_N, а затем изменить значения в main_entity.
- И что вы будете делать, если вы создали запись в таблице additional_entity_1, а в main_entity уже указан id additional_entity_1_id?

2. Во втором варианте у вас ошибки:
- У вас связь 1-ко-многим, а не один к одному.
- У таблиц additional_entity_N автоинкремент не нужен, поле id нужно убрать.
- Primary key в таблицах additional_entity_N должен быть main_entity_id.
- Во всех 4х таблицах должен быть одинаковый ID Primary key.

3. Если исправить ошибки во втором варианте, то корректнее использовать его.