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

Question

Database design

The crux of the matter is that there is an entity that is stored in 1 table, let's call it workers (workers), and it is necessary to maintain another table in which to store the relationship, who worked with whom at time intervals (woker_relations) . 2 organization options come to mind, and in the first and second, the workers table is the same, but in the second table, either such 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, it seems to be architecturally correct, you can impose uniqueness on the fields by id and date (although it seems that not all subds allow this for the date), but there will be data duplication (worker 1 will have a connection with worker 2 and vice versa). In the second case, there will be no duplication, but queries for selections become more complicated (you have to write queries like: ... where worker_1_id = 123 or worker_2_id = 123) and you will have to make an additional request for uniqueness before inserting. Maybe there are some other options? Thanks for the answer. And another question arises with ORM mapping) ...

Answer 1

Consider adding an additional "workgroup" entity with "start date" and "end date" attributes.
In the general case, this is how non-standard situations overlap, like
- someone got sick and the partner worked on his own
- a third was added to the group
- someone works on two projects
But here it depends on the specific business case. You need to know the 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

PS In your options, the "link id" field seems to be redundant. The uniqueness of the connection is done by ( worker_1_id, worker_2_id, start_date).