Create Additional Table for Many to Many Relationship in MySQL Database Server

This is an article which is made to display some sort of explanation on creating an additional table for many to many relationship. As it has already known that creating additional table is necessary to describe a many to many relationship.

The scenario is described in detail as follows :

If there are many users whom are registered to a system where those users are also have lots or more than one roles in the system. Below is actually the scenario for creating the tables. In this context, the database which is used is MySQL. First of all, create the user table :

CREATE TABLE user
(  
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),   
    Title VARCHAR(255)
)

And after that, create the other table which is made to manage the roles available. It is shown as follows :

CREATE TABLE role
(  
    id INT NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY(id),  
    Name VARCHAR(255)
)

The last one is actually the most important table which is used to relate and to attach both the tables created in the previous steps. This table is created to maintain the correlation between user table and role table.

CREATE TABLE user_role
(
    id_user INT NOT NULL,  
    id_role INT NOT NULL,  
    PRIMARY KEY (id_user, id_role),  
    FOREIGN KEY (id_user) REFERENCES user(id) ON UPDATE CASCADE,  
    FOREIGN KEY (id_role) REFERENCES role(id) ON UPDATE CASCADE
)  

As shown above, there are two additional informations which is very important in order to maintain the connection and the correlation between the users and the roles which is actually owned or possessed by those users. Below are the examples using the above script which has already been executed. It will create the following tables :

The first table is a table named ‘user’ :

mysql> desc user;
+-----------+--------------+------+-----+--------+----------------+
| Field     | Type         | Null | Key | Default| Extra          |
+-----------+--------------+------+-----+--------+----------------+
| id_user   | int(5)       | NO   | PRI | NULL   | auto_increment |
| username  | varchar(150) | NO   |     | NULL   |                |
| password  | varchar(255) | NO   |     | NULL   |                |
+-----------+--------------+------+-----+--------+----------------+
3 rows in set (0,01 sec)

The following is the output of a the above table which is presenting several records of it :

mysql> select * from user;
+-------+--------+---------------------------------+-------------+
|id_user|username| password                        |name         |
+-------+--------+---------------------------------+-------------+
|     7 | james  | c53e479b03b3220d3d56da88c4cace20|James Cameron|
|     8 | david  | 21232f297a57a5a743894a0e4a801fc3|David Bowie  |  +-------+-------------+----------------------------+-------------+
2 rows in set (0,05 sec)
mysql>

The second table, is the table named ‘role’. Below is the description of the table named ‘role’ :

mysql> desc role;
+---------+--------------+------+-----+---------+---------------+
| Field   | Type         | Null | Key | Default | Extra         |
+---------+--------------+------+-----+---------+---------------+
| id_role | int(11)      | NO   | PRI | NULL    | auto_increment|
| name    | varchar(255) | YES  |     | NULL    |               |
+---------+--------------+------+-----+---------+---------------+
2 rows in set (0,00 sec)
mysql> 

Below is how to retrieve information about the content or records exist in table ‘role’ using the select query command :

mysql> select * from role;
+---------+------------------------+
| id_role | name                   |
+---------+------------------------+
|       1 | System Administrator   |
|       2 | Application Developer  |
|       3 | Database Administrator |
+---------+------------------------+
3 rows in set (0,01 sec)
mysql> select * from user_role;
Empty set (0,01 sec)
mysql> 

So, how is the way or the suitable method to maintain data containing list of users whom has lots of role or having multiple roles ?. To be able to do it, a way which can be pursued is by creating a third table which is saving the identifier of each table from table ‘user’ and table ‘role’.

Below is the description of the tables named ‘user_role’ :

mysql> desc user_role;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id_user | int(11) | NO   | PRI | NULL    |       |
| id_role | int(11) | NO   | PRI | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0,00 sec)

The content of the table above can be shown in the following output using the ‘select’ query :

 mysql> select * from user_role;
+---------+---------+
| id_user | id_role |
+---------+---------+
| 7       | 1       |
| 7       | 2       |
| 8       | 2       |
| 8       | 3       |
+---------+---------+
4 rows in set (0,01 sec)
mysql>

As shown in the above output, user which has an id_user of 7, in this case james has two roles. The first one is as ‘System Administrator’ and the other role is as an ‘Application Developer’. On the other hand, there is another user which has an id_user of 8, retrieved from the table named ‘user’, david whom also has two roles. The user ‘david’ has a role as an ‘Application Developer’ and as a ‘Database Administrator’.