0

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?

3
  • normalization means only one table for each data element. relation database tables aren't hierarchical. So separate tables or a single table, depending on if you need to treat all users together. – danblack Nov 27 '20 at 9:57
  • We tried to have our tables in 3rd normal form. So what I want to accomplish is if I was to delete user, then all its info gets deleted as well in other tables such as student if the user is a student. I previously used on delete cascade but did not work and data remained. – Random598345702 Nov 27 '20 at 20:59
  • In the case that I do not define keys, would the relations still work? Could that have been an issue? – Random598345702 Nov 27 '20 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.