How to implement and insert value SQL specialization/generalization

Question

I have my table with these 3 entities: student, lecturer and person. Person will be the parent that holds common attributes of student and lecturer. This is my database design, is this a correct way of implementing specialization?

Person

personID int Primary key,
Role varchar(10) ,
Name varchar(50),
email varchar(255)

Student

studentID int,
intake varchar(50),
major varchar( 50) ,
personID int reference person(personID)

Lecturer

LecturerID int ,
Skill varchar(50),
Qualification varchar(50) ,
personID int reference person(personID)

The above of the table, is this the correct way to implement generalization/specialization ? If it is, How do I insert a new value to the database when a new student enrolls?

Answer 1

You generally have the right idea (or, at least, one of the common idioms to implement such a database schema). If your rdbms supports it, I'd use an enum for the role column (MySQL, e.g., does. MS SQL Server does not). If it doesn't I'd add a check constraint to achieve a similar effect and make sure you don't get any junk inserted there.

Insertion should be done to the base table (person) first and only then to the specialization tables (student or lecturer). E.g.:

INSERT INTO person 
VALUES (1, 'student', 'hiboss', '[email protected]');

INSERT INTO student
VALUES (100, 'intake', 'computer science', 1);