shank shank - 3 months ago 16
MySQL Question

How to create many to many relationship in mysql

I believe the title is self-explanatory. How do you create the table structure in MySQL to make a many-to-many relationship.

My tables are:

pr_resignations_request(
id, userids ,managerid ,resignations_date ,reason_type requested_date, last_status, date_last_status, agreed_date exit_details, exit_checklist, exit_type, comments, discussion, withdrawn, manager_comments
);

pr_exit_checklist(
checklist_id, category, action item, responsibility, time_completion, status
);


I am trying to link the two tables such that foreach resignation request there are multiple checklist items. Also the checklist items can grow and shrink when added and deleted and also resignation request table can grow and shrink when added and deleted. FYI I am using codeigniter framework, I have No clue how to achieve this.

Answer

Create a Pivot table with foreign key's of both the related Tables.
Let's say you have a Product table with columns id and name. Also a Cart Table having columns id and name.
To create a many to many relationship between product and cart table, create a intermediate table with columns product_id(foreign key referring to id column of Product table) and cart_id(foreign key referring to id column of Cart table)

Update 1:

In your case, the intermediate table would contain pr_resignations_request_id (foreign key referring to id column of pr_resignations_request table) and pr_exit_checklist_id (foreign key referring to checklist_id column of pr_exit_checklist table)

Update 2:

CREATE TABLE checklist_resignation (
pr_resignations_request_id <data_type>,
pr_exit_checklist_id <data_type>,
Constraint FOREIGN KEY (pr_resignations_request_id) REFERENCES pr_resignations_request(id),
Constraint FOREIGN KEY (pr_exit_checklist_id) REFERENCES pr_exit_checklist(checklist_id)
);

Please replace the <data_type> in above sql statements to the data type of the columns they are referring to respectively.

Comments