Creating database tables having many to many field relations

Last Updated on

There are cases in designing a database where the value of one particular field (say, Id field) of a first table holds association with many possible values of a field (say, Id) in a second table, and vise-versa, that is, the second table also has this same field (Id) associated with many possible Ids of the first table. An example is a database that has two tables: Users, and Roles: One user can have many Roles, and one Role can be associated with many users. Another example is the example of a subscription database: one subscriber can have association with several magazines and one magazine has association with many subscribers. A third example would be a movies and categories database: the movies table can be included in many categories and one category would include many movies. This type of table design in a database is what is called as designing a many-to-many SQL relationship.

Below is a review on preparing such tables and their relationship and to insert and retrieve records accordingly.

An example where the designer uses JPA, Hibernate and Spring Boot to utilize a many-to-many relationship with Users and Roles is here.

Another example where the many-to-many relationship is discussed and how one can possibly have a third table which has a compound primary key to hold this relationship is discussed here.

Here is the SQL queries for preparing two tables, a movie table and a category table, and a conjunction table for the many-to-many relationship:

create table Movie (Movie_ID int not null auto_increment, Name varchar(255), primary key(Movie_ID));
create table Category (Category_ID int not null auto_increment, Name varchar(255), primary key (Category_ID));
create table movie_category_junction (
movie_id int,
category_id int,
CONSTRAINT movie_cat_pk PRIMARY KEY (movie_id, category_id),
CONSTRAINT FK_movie FOREIGN KEY (movie_id) REFERENCES movie (movie_id), CONSTRAINT FK_category FOREIGN KEY (category_id) REFERENCES category (category_id) );

Now we can insert records to the first and second table, as well as the junction table that holds the relationships between the two tables:

INSERT INTO movie (movie_id, name) VALUES (8, "Movie on SQL development");
INSERT INTO movie (movie_id, name) VALUES (9, "Movie on Java EE application development");


INSERT INTO category (category_id, name) VALUES (10, "SQL Development");
INSERT INTO category (category_id, name) VALUES (11, "Java EE Development");




INSERT INTO movie_category_junction (movie_id, category_id) VALUES (8,10);
INSERT INTO movie_category_junction (movie_id, category_id) VALUES (8,11);

INSERT INTO movie_category_junction (movie_id, category_id) VALUES (9,10);
INSERT INTO movie_category_junction (movie_id, category_id) VALUES (9,11);

Another example would be: https://stackoverflow.com/questions/19714308/mysql-how-to-insert-into-table-that-has-many-to-many-relationship

And to select records from the two table we would use LEFT JOIN.

To get a movie with a given Id (say, movie with Id 8:

SELECT m.*, c.* FROM movie as m
LEFT JOIN movie_category_junction as mc ON m.movie_id = mc.movie_id
LEFT JOIN category AS c on mc.category_id = c.category_id
WHERE m.movie_id = 8;

To get a movie with a given Id 9:

SELECT m.*, c.* FROM movie as m
LEFT JOIN movie_category_junction as mc ON m.movie_id = mc.movie_id
LEFT JOIN category AS c on mc.category_id = c.category_id
WHERE m.movie_id = 9;

And to get the category with a given category Id, say category 11, we would do:

SELECT m.*, c.* FROM category as c 
LEFT JOIN movie_category_junction as mc ON c.category_id = mc.category_id
LEFT JOIN movie AS m on mc.movie_id = m.movie_id
WHERE c.category_id = 11;

Many to many relationships occur where two tables have fields that can be associated with more than one field in both ways. This is where a many to many relationship can be applied via SQL. In the above example, a third table was created to hold the relationship.

Lämna ett svar