Как сделать связь многие к многим для одной таблицы?

Question

[santaatnas]

Суть вопроса, есть сущность, которая хранится в 1 таблице, назовем ее работники (workers), и необходимо вести еще одну таблицу, в которой хранить связь, кто с кем работал с интервалами времени (woker_relations) . На ум приходит 2 варианта организации, и в первом и во втором, таблица workers одинакова, а вот во второй таблице либо такие поля (id, worker_id, partner_id, start_date, end_date) либо (id, worker_1_id, worker_2_id ,start_date, end_date). В первом случае, вроде архитектурно правильно, можно наложить уникальность на поля по id и дате (хотя вроде бы не все субд позволяют это применительно к дате), но будет происходить дублирование данных (у worker 1 будет связь с worker 2 и наоборот). Во втором же случае дублирование не будет, но усложняются запросы на выборки (приходится писать запросы вида: ...where worker_1_id = 123 or worker_2_id = 123) и придется делать доп запрос на уникальность перед вставкой. Может есть еще какие-то варианты? Спасибо за ответ. И еще вапрос с ORM маппингом возникает)...

Comments

[Модератор]

Дорогой пользователь, настоятельно рекомендуем еще раз обратить самое пристальное внимание на п. 3.1 регламента работы сервиса (и, в особенности, на его последний абзац).
В противном случае, ваши вопросы будут удаляться по причине тег-спама, а систематические нарушения приведут к блокировке учетной записи.

Answer 1

[Максим Y]

Подумайте над добавлением дополнительной сущности "рабочая группу" с атрибутами "дата начала" и "дата конца".
В общем случае, так перекрываются нестандартные ситуации, вроде
- кто-то заболел и напарник работал сам
- в группу добавили третьего
- кто-то работает на двух проектах

Но тут от конкретного бизнес-кейса зависит. Надо знать ограничения.

#team

team_id	|	start_date	|	end_date
--------------------------------------------------
	1	|	01.01.2018	|	05.01.2018

#team_worker

team_id	|	worker_id	
--------|---------------
	1	|	101			
	1	|	102

P.S. В ваших вариантах поле "ид связи", похоже, избыточное. Уникальность связи делают по ( worker_1_id, worker_2_id, start_date).

Comments

[santaatnas]

А как в вашем варианте будет выглядеть выборка всех, кто работал с человеком? Допустим зная id работника нужно знать по периодам с кем он работал. Забыл уточить по задаче, что человек может одновременно работать только с одним человеком в паре. Вот ещё как пример нашёл подобное решение, люди с гирбицидами реализовывают мой 2й вариант. www.adslclub.ru/forum/topic37534

[Rsa97]

Допустим зная id работника нужно знать по периодам с кем он работал.

Примерно так. Причём работать будет независимо от количества работников в группе:

SELECT `c`.`team_id`, `t`.`start_date`, `t`.`end_date`, `c`.`colleagues`
  FROM (
    SELECT `w`.`team_id` AS `team_id`, 
           GROUP_CONCAT(`c`.`worker_id`) AS `colleagues`
      FROM `team_worker` AS `w`
      JOIN `team_worker` AS `c`
        ON `w`.`worker_id` = :workerId
          AND `c`.`team_id` = `w`.`team_id` 
          AND `c`.`worker_id` != `w`.`worker_id`
      GROUP BY `w`.`team_id`
  ) AS `c`
  JOIN `team` AS `t`
    ON `t`.`team_id` = `c`.`team_id`

[santaatnas]

Rsa97, Вот этого я и боялся, как результат такого запроса смапить на объект, и так же по производительности возникают вопросы))

[Rsa97]

santaatnas, С производительностью у такого запроса всё хорошо, он нормально использует индексы.

[Максим Y]

santaatnas,
> как в вашем варианте будет выглядеть выборка всех, кто работал с человеком?

В моем варианте вот так:

select
 tw.worker_id
,tw2.worker_id as partner
,t.dt_from
,t.dt_to
from as_team_worker tw
inner join team t on t.team_id = tw.team_id
inner join as_team_worker tw2 on tw2.team_id = tw.team_id
                             and tw2.worker_id != tw.worker_id

where tw.worker_id = 5

Где вы видите потенциальную просадку по производительности? Приведите пример из жизни планируемого приложения.
Да, будут хорошо использоваться индексы.

[Максим Y]

Люди с гербицидами тоже потоптались по граблям, пытаясь реализовать симметричную связь несимметричным методом :)

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

[santaatnas]

Максим Sh, спасибо за ваши ответы.