0

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!

New contributor
Interrobang is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

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' )
    );
| improve this answer | |
  • 1
    You got it on the head! Thanks man. Only thing I had to change was name, and it added an entry no problem. The reason I want to keep it as 3 distinct tables is for the future, if I need more data for Roles, or need to change a role name, I can do it with one change, not iterate through all users that have a role, etc. – Interrobang Jun 27 at 17:24
  • @Interrobang - Glad you understand the concepts. – Rick James Jun 27 at 17:28
0

Even if you have a UNIQUE constraint on username.

You must replace all referenced Columns with the username and change the dataatype of column.

A text field is also usually slower that an Integer.

So when you see all negative points, there is no good reason to change it now.

Besides, what does it really matter, if you have a numeric id that you have to use to identify the user or another unique text filed.

| improve this answer | |

Your Answer

Interrobang is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.