SQL Server - Question about Inheritance in Database Schemas-CodePudding

I'm having problems understanding the class table inheritance structure that you can implement using database tables.

Right now, a Professor can only have one person, for example, otherwise it wouldn't make sense in my use case. Also, I have a school table that has two foreign keys, one for the Professor and one for the Student. Lets assume that a school can also have only one professor and one student. This is not the real use case that I have. This example just represents the relation in my real use case which would be too much to explain here.

What I don't understand is how you would collect data based on that. I'm trying to make a SQL Server View where I want to load the Person of the Professor and the Person of the Student from the view point of the School Table. For example:

SELECT 
    School.professor_id
    surname,
    lastname
FROM dbo.School AS school

INNER JOIN dbo.Professor as prof
    ON school.professor_id = prof.ID
INNER JOIN dbo.Person as prof_person
    ON prof.person_id = prof_person.ID

I can output the surname and lastname of the professor, but now I am stuck since I can't figure out how to get the person of the student.

CodePudding user response：

A subtype table typically shares a key with the supertype table, instead of having its own PK and a FK. EG Student.ID is both the PK and the FK.

Then just join Student>Person in addition to Professor>Person, eg

SELECT 
    School.Id
    prof_person.surname prof_surname,
    student_person.surname student_surname
FROM dbo.School AS school
INNER JOIN dbo.Professor as prof
    ON school.professor_id = prof.ID
INNER JOIN dbo.Person as prof_person
    ON prof.ID = prof_person.ID
INNER JOIN dbo.Student as student
    ON school.student_id = student.ID
INNER JOIN dbo.Person as student_person
    ON student.ID = student_person.ID