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!