How to make a many-to-many one table?

The crux of the matter, is the substance that is stored in 1 table, let's call it employees (workers) to be broken, and another table that store the relationship, with someone who worked with time intervals (woker_relations) . Comes to mind 2 years, and in the first and second table workers are the same, but the second table or the following fields (id, worker_id, partner_id, start_date, end_date) or (id, worker_1_id, worker_2_id ,start_date, end_date). In the first case, like architectural correctly, you can impose uniqueness on a field by id and date (although it seems not all DBMS allow a reference to a date), but there will be data duplication (worker 1 communication worker with 2 and Vice versa). In the second case, the duplication will not, but more complicated requests for samples (you have to write queries of the form: ...where worker_1_id = 123 or worker_2_id = 123) and will have to make extra query for uniqueness before inserting. Maybe there are some other options? Thanks for the reply. And vapros with ORM mapping occurs)...
June 3rd 19 at 18:57
1 answer
June 3rd 19 at 18:59
Solution
Consider adding additional entities "working group" with the attributes "start date" and "end date".
In the General case, overlapping unusual situations, like
someone became ill and the partner worked himself
- the group added a third
- someone is working on two projects

But there is a specific business case depends. It is necessary to know limitations.

#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. In your options box, the "link IDs" seems redundant. The uniqueness of communication ( worker_1_id, worker_2_id, start_date).
And how your form will look the sample of all who worked with the man? For example knowing the id of the employee you need to know for the periods with whom he worked. Forgot to contact the task that a person can simultaneously work with only one person in the pair. Here's another as an example found that people with girbitsidov implement my 2nd option. www.adslclub.ru/forum/topic37534 - jermaine44 commented on June 3rd 19 at 19:02
For example knowing the id of the employee you need to know for the periods with whom he worked.

Like this. And will work regardless of the number of employees in the group:
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`
- Kenyatta commented on June 3rd 19 at 19:05
Here I was afraid of that, as a result of such a request snapit on the subject, and as performance issues arise)) - jermaine44 commented on June 3rd 19 at 19:08
With the performance of this query, all is well, he normally uses indexes. - Kenyatta commented on June 3rd 19 at 19:11
,
> in your option will look like the sample everyone who worked with the person?

In my version here it is:

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

Where do you see potential drawdown performance? Give an example from the life plan of the app.
Yes, there are good indexes are used. - Cleta73 commented on June 3rd 19 at 19:14
People with herbicides also have tread on the rake, trying to implement a symmetric connection asymmetric method :)

Of your two options is acceptable the one where two records for each connection (symmetric). Should be much less code to implement. And it's the fastest for selection.
If workarou always two will be the norm. - Cleta73 commented on June 3rd 19 at 19:17
thank you for your answers. - jermaine44 commented on June 3rd 19 at 19:20

Find more questions by tags Database design