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);