MySQL unique id or combined id

Question

I have the following structure for my project and developer program:

developer table
id
developer name
etc...

project table
id
project name
etc...

developer _project table
???

Because a developer can be in multiple projects and a projects can have multiple developer I have to create a new table but from what I read is they don't tell what id should I use.

If I use an id auto increment + user_id + project_id will I have duplicate since the id is the primary key?

Answer 1

Use a unique combined key:

CREATE TABLE `developer_project` (
 developer_id INT(10) UNSIGNED /* etc... */,
 project_id INT(10) UNSIGNED /* etc... */,
 PRIMARY KEY dev_project (developer_id, project_id)
);

If you create an ID you will probably never use it since you will query the developer_id and/or project_id in your LEFT JOIN

NOTE: make sure the column definitions are the same as the developer and the project tables.

Answer 2

for many-to-many relationship, you could simply use composite primary key of two fields.

Example (assuming both project_id and developer_id is integer):

CREATE TABLE `developer_project` (
  `developer_id` INT NOT NULL ,
  `project_id` INT NOT NULL ,
  PRIMARY KEY (  `developer_id` ,  `project_id` )
)

Answer 3

A combined table (in MySQL workbench automatically called "developer_has_project") should use a combined primary key (developer,project). If you add a third column to this key (ID) it no longer has to be unique:

(id,developer,project)
(1,1,1)
(2,1,1)
(3,1,1)

Using only the developer and project, it will work:

(developer,project)
(1,1)
(1,1) <-- error!
(2,1)

Alternatively you could use the ID as the only primary key, and add a UNIQUE restraint on (developer,project):

(id,developer,project)
(1,1,1)
(2,1,1) <-- error
(3,2,1)