Database Inheritance Select

Question

For my university assignment I have to design some basic managment system for sicknesses and all for a school. I have decided to model some basic inheritance in the form of

• Person --> Student
• Person --> Staff
• Person --> Guardian

Person (PersonID, FirstName, LastName)

Student (StudentID (Which references the PersonID), ... )

The reason i decided to do this as I modeled this first in UML and had the inheritance in this.

I have another table which stored Incidents which have both StudentID, StaffID and GuardianID. However I was wondering how I would create a join in mysql which would display all three inherited people's names?

e.g.

Student.FirstName Student.LastName, Staff.FirstName, Staff.LastName etc...

How would I do this?

Or am i doing this completely wrong this way?

Thanks in advance.

http://pastebin.com/m263dd7 - Link to my DDL for the tables.

Answer 1

This is not right. You should have a Person class, and other classes would determine that a certain Person is a Student, Staff, etc. What happens if you have a staff person that is also a student? What happens if the Student graduates?

It is a classic example of impedance mismatch between the relational model and the OO model.

You could have for example three tables:

PERSON
PersonId LastName FirstName

STUDENT StudentId PersonId

STAFF
StaffId PersonId

Answer 2

I dont know what the real usage, but it is not a good idea to use inheritence just for reusability.

At first sight, having a Person class in a university management system (something similar) does not seem right.

It would be better you mention the objective/purpose of the assignment - what is is supposed to do.

Answer 3

I have no problem with the database design you've described. It's always a bit awkward to model inheritance in SQL, but you've used the least problematic solution.

Here's a query to answer your question about retrieving the names of a student and staff member for a given incident:

SELECT ps.FirstName, ps.LastName, pf.FirstName, pf.LastName
FROM Incidents i
 JOIN Students s USING (student_id)
 JOIN Persons ps ON (s.student_id = ps.person_id)
 JOIN Staff f USING (staff_id)
 JOIN Persons pf ON (f.staff_id = pf.person_id)
WHERE i.incident_id = ?;

I'm assuming the Incidents table looks includes columns such as:

CREATE TABLE Incidents (
  incident_id SERIAL PRIMARY KEY,
  student_id  INT NOT NULL,
  staff_id    INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES Students(student_id),
  FOREIGN KEY (staff_id) REFERENCES Staff(staff_id)
);

Realistically, I'd expect some kind of many-to-many relationship between incidents and each of staff and students. Otherwise, an incident can involve only one student and one staff member?