Mysql – Help with Many to Many Table Data Entry

MySQLquery

I've got a question on how to "prettify" data entry and removal for a many to many relationship.

Imagine if you will a DB with 3 tables. Tables called Users, Roles, and RoleAssignment.

-- `Users`    
CREATE TABLE `Users`
(
 `userID`   int NOT NULL AUTO_INCREMENT ,
 `userName` varchar(40) NOT NULL ,
 `realName` varchar(40) NOT NULL ,

PRIMARY KEY (`userID`),
UNIQUE KEY `username` (`username`)
) AUTO_INCREMENT=1;


-- `Roles`
CREATE TABLE `Roles`
(
 `roleID`       int AUTO_INCREMENT NOT NULL ,
 `roleName`     VARCHAR(40) NOT NULL ,

PRIMARY KEY (`roleID`)
);


-- `RoleAssignment`

CREATE TABLE `RoleAssignment`
(
 roleAssignID int NOT NULL AUTO_INCREMENT ,
 roleID       int NOT NULL ,
 userID       int NOT NULL ,

PRIMARY KEY (roleAssignID),
FOREIGN KEY (userID) REFERENCES Users(userID),
FOREIGN KEY (roleID) REFERENCES Roles(roleID)
) AUTO_INCREMENT=1;

This is a database where RoleAssignment is essentially a child table only meant to connect two Parent tables together. I made them this way because the user<->role relationship is many to many.

I found a way of getting this to output just relationships between users and their roles… Both queries output the same data but query 2 is better organized.

SELECT
    Users.userName,
    Users.realName,
    Roles.roleName
FROM Users
JOIN RoleAssignment ON Users.userID = RoleAssignment.userID
JOIN Roles ON Roles.roleID = RoleAssignment.roleID;

SELECT
Users.userName,
Users.realName,
GROUP_CONCAT(Roles.roleName)
FROM Users
JOIN RoleAssignment ON Users.userID = RoleAssignment.userID
JOIN Roles ON Roles.roleID = RoleAssignment.roleID
GROUP BY Users.userID;

Is there an easy way to add an entry to the RoleAssignment table by name instead of ID in a single query? i.e. "Add user jack to role moderator"? Or would I need to write a script to find the ID of the user, then the ID of the role, and finally add an entry to RoleAssignment?

Any help or guidance would be appreciated!

Best Answer

3 tables is the standard way to implement many:many between two "entities" (users and roles, in your example).

However, if Roles is just an id and a role, you could get rid of it by simply putting the role name in the mapping table. ("Tagging" is another example -- there is no real need to instantiate the "tags" table; just put the tags separately in the mapping table.)

Get rid of the id in the mapping table; it serves no purpose. With those changes, you get

CREATE TABLE `RoleAssignment`
(
    role VARCHAR(99) NOT NULL,
    userID       int NOT NULL ,
    PRIMARY KEY(userID, role),   -- For finding roles for a user
    INDEX      (role, userID)    -- For finding users for a role
) ENGINE=InnoDB;

The FOREIGN KEYs are of little use in this context.

More on mapping table design: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Don't worry about using VARCHARs in indexes. Sure, you pay a little in speed, but gain a lot in simplicity, and even more if you are not having to map to an INT before using the table.

"Add user jack as a moderator"

By using the INSERT..SELECT syntax, it is not very complex; a single query suffices:

INSERT INTO RoleAssignment
    (userID, role)
    SELECT userID, 'moderator'
        FROM Users
        WHERE name = 'Jack';

If you choose to have Roles in a separate table, a different approach could be used. This takes advantage of being able to use a parenthesized SELECT in place of a literal.

INSERT INTO RoleAssignment
    ( userID,
      role
    )
    VALUES
    ( ( SELECT userID FROM Users WHERE name = 'Jack' ),
      ( SELECT roleID FROM Roles WHERE name = 'moderator' )
    );
Related Question