Help with Many to Many Table Data Entry I've got a question on how to &quot;prettify&quot; 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&lt;-&gt;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. &quot;Add user jack to role moderator&quot;? 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!
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.