I currently have a MySQL database and our ERD has inheritance for various tables. One is User which has as children: administrators, students and professors. The tables look as follow:
CREATE TABLE `Users` ( `id_number` int DEFAULT NULL, `First_name` varchar(50) DEFAULT NULL, `Last_name` varchar(50) DEFAULT NULL, `User_Type` varchar(14) DEFAULT NULL ) ENGINE=InnoDB CREATE TABLE `Advisor` ( `Faculty_id` varchar(2) DEFAULT NULL, `Student_id` varchar(5) DEFAULT NULL, `Date_Assigned` date DEFAULT NULL ) ENGINE=InnoDB CREATE TABLE `Faculty` ( `Faculty_id` varchar(13) DEFAULT NULL, `Room_id` varchar(2) DEFAULT NULL, `Faculty_Speciality` varchar(16) DEFAULT NULL, `Faculty_Rank` int DEFAULT NULL, `Faculty_Type` varchar(9) DEFAULT NULL, `user_type` varchar(13) DEFAULT NULL ) ENGINE=InnoDB
I’m aware that things such as
PRIMARY KEY are missing for the ids and
REFERENCES for foreign keys. Similar is found on this post How to model inheritance of two tables MySQL. However, what I want to know is when adding users how to populate both tables (parent and children)? Meaning that both tables user and student get the same information? How would this work on data that gets over 3000 students?