On signup form INSERT -> update corresponding values in another table

From usersTable there are userRoles. — Teacher, Mentor, Student.

When a user submits signup form, they are either one of the three.

I also have three more tables, — teacherTable, mentorTable, studentTable.

These three tables have FK’s for “userID” which is the PK in userRoles.

Is there a way to auto INSERT name, lastname, email etc. into the teacher, mentor or student tables (depending on the userRole) so that the name, lastname, email etc. match with the “userID” from the main userTable?

I want some way to efficiently do this, rather than having to use INSERT multiple times and making it very long. Can anyone show me how to do this simply as I am very new to this.

CREATE TABLE userTable (
    userID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userRole enum('admin','teacher','mentor','student') NOT NULL DEFAULT 'student',
    houseID enum('1','2','3','4') NOT NULL,
    firstName varchar(100) NOT NULL,
    lastName varchar(100) NOT NULL,
    gender enum('Male','Female') NOT NULL,
    yearLevel int,
    emailAddress varchar(100) NOT NULL,
    user_username varchar(100),
    user_password varchar(100) NOT NULL,
    userTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE teacherTable (
    teacherID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userID int NOT NULL,
    firstName varchar(100) NOT NULL,
    lastName varchar(100) NOT NULL,
    emailAddress varchar(100) NOT NULL
);

CREATE TABLE mentorTable (
    mentorID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userID int NOT NULL,
    firstName varchar(100) NOT NULL,
    lastName varchar(100) NOT NULL,
    emailAddress varchar(100) NOT NULL,
    gender enum('Male','Female') NOT NULL,
    yearLevel int NOT NULL
);

CREATE TABLE studentTable (
    studentID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userID int NOT NULL,
    firstName varchar(100) NOT NULL,
    lastName varchar(100) NOT NULL,
    emailAddress varchar(100) NOT NULL,
    gender enum('Male','Female') NOT NULL,
    yearLevel int NOT NULL
);

ALTER TABLE teacherTable
    ADD CONSTRAINT teacherTable_fk_1 FOREIGN KEY (userID) REFERENCES userTable (userID);

ALTER TABLE mentorTable
    ADD CONSTRAINT mentorTable_fk_1 FOREIGN KEY (userID) REFERENCES userTable (userID),
    ADD CONSTRAINT mentorTable_fk_2 FOREIGN KEY (gender) REFERENCES userTable (gender);

ALTER TABLE studentTable
    ADD CONSTRAINT studentTable_fk_1 FOREIGN KEY (userID) REFERENCES userTable (userID),
    ADD CONSTRAINT studentTable_fk_2 FOREIGN KEY (gender) REFERENCES userTable (gender);

​x
 
CREATE TABLE userTable (    userID int NOT NULL PRIMARY KEY AUTO_INCREMENT,    userRole enum('admin','teacher','mentor','student') NOT NULL DEFAULT 'student',    houseID enum('1','2','3','4') NOT NULL,    firstName varchar(100) NOT NULL,    lastName varchar(100) NOT NULL,    gender enum('Male','Female') NOT NULL,    yearLevel int,    emailAddress varchar(100) NOT NULL,    user_username varchar(100),    user_password varchar(100) NOT NULL,    userTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);​CREATE TABLE teacherTable (    teacherID int NOT NULL PRIMARY KEY AUTO_INCREMENT,    userID int NOT NULL,    firstName varchar(100) NOT NULL,    lastName varchar(100) NOT NULL,    emailAddress varchar(100) NOT NULL);​CREATE TABLE mentorTable (    mentorID int NOT NULL PRIMARY KEY AUTO_INCREMENT,    userID int NOT NULL,    firstName varchar(100) NOT NULL,    lastName varchar(100) NOT NULL,    emailAddress varchar(100) NOT NULL,    gender enum('Male','Female') NOT NULL,    yearLevel int NOT NULL);​CREATE TABLE studentTable (    studentID int NOT NULL PRIMARY KEY AUTO_INCREMENT,    userID int NOT NULL,    firstName varchar(100) NOT NULL,    lastName varchar(100) NOT NULL,    emailAddress varchar(100) NOT NULL,    gender enum('Male','Female') NOT NULL,    yearLevel int NOT NULL);​ALTER TABLE teacherTable    ADD CONSTRAINT teacherTable_fk_1 FOREIGN KEY (userID) REFERENCES userTable (userID);​ALTER TABLE mentorTable    ADD CONSTRAINT mentorTable_fk_1 FOREIGN KEY (userID) REFERENCES userTable (userID),    ADD CONSTRAINT mentorTable_fk_2 FOREIGN KEY (gender) REFERENCES userTable (gender);​ALTER TABLE studentTable    ADD CONSTRAINT studentTable_fk_1 FOREIGN KEY (userID) REFERENCES userTable (userID),    ADD CONSTRAINT studentTable_fk_2 FOREIGN KEY (gender) REFERENCES userTable (gender);​

Answer

Use a stored procedure. But as @Barmar pointed out, you shouldn’t duplicate the data. The only columns that belong in the teacherTable, mentorTable and studentTable are for information that is unique to those roles. So, for example a Teacher might have an employee number and department number, and status level (“full professor, etc), while a student might have a Student ID that might even overlap the employee ID numbers.

But back to using stored procedures… not only can you insert into multiple tables, you avoid issues of SQL injection. Note that after you insert into your userTable, you will want the stored procedure to save the last id entered using SELECT LAST_INSERT_ID() in order to use that to create the record in the second table entry.

Advertisement

Answer