SQL Server - Question about Inheritance in Database Schemas

Question

I'm having problems understanding the class table inheritance structure that you can implement using database tables. Info on class table inheritance. I have a use case where I have quite different types of persons that I need to model, but they have very minor differences. For example, all of these persons, like Student, Professor and so on, have a surname and a lastname. My first thought was to move theses attributes into a different table inside a base table like you would do in Object Oriented Programming. Here to illustrate further:

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.

Properly qualify your column names, @Florent, like you should be doing anyway. — Thom A, Feb 03 '22 at 17:14
*"The proper column name would be Person.surname"* wrong, in your above query that would error as you aliased `Person` as `prof_person`. I know the answer, but with the prior comment, I'm out — Thom A, Feb 03 '22 at 17:52
Why do you have a surname and a lastname? Where is the firstname? — Paul Alan Taylor, Feb 03 '22 at 18:11
You need to learn about using nice and short *and meaningful* table aliases, they make queries much more readable. You could pick `pProf` and `pStud` as aliases for the two joins — Charlieface, Feb 03 '22 at 22:15
@PaulAlanTaylor Does it matter? Its about the problem here not about if I have surname lastname etc, they are just columns you know. — Florent, Feb 04 '22 at 07:40

score 2 · Accepted Answer · edited Feb 03 '22 at 19:46

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

INNER JOIN is associative, so no need for special ordering or parentheses.

SQL Server - Question about Inheritance in Database Schemas

1 Answers1