For my college assignment I must design some fundamental managment system for illnesses and all sorts of for any school. I've made the decision to model some fundamental inheritance as

  • Person --> Student
  • Person --> Staff
  • Person --> Protector

Person (PersonID, FirstName, LastName)

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

The main reason i made the decision to get this done when i patterned this primary in UML coupled with the inheritance within this.

I've another table which saved Occurrences that have both StudentID, StaffID and GuardianID. However I'm wondering the way i would produce a participate in mysql which may display the 3 inherited individuals names?

e.g.

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

Wouldso would I actually do this?

Or shall we be held carrying this out drastically wrong by doing this?

Thanks ahead of time.

http://pastebin.com/m263dd7 - Connect to my DDL for that tables.

I've not a problem using the database design you've referred to. It certainly is a little awkward to model inheritance in SQL, but you've used minimal problematic solution.

Here is a query to reply to your question about locating what they are called of the student and employee for any 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 am presuming the Incidents table looks includes posts for example:

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)
);

Reasonably, I'd expect some type of many-to-many relationship between occurrences and every of staff and students. Otherwise, an accidents can involve just one student and something employee?

This isn't right. You ought to have an individual class, along with other classes would determine that the certain Individual is students, Staff, etc. What goes on for those who have an employee person that's additionally a student? What goes on when the Student graduates?

It's a classic illustration of impedance mismatch between your relational model and also the OO model.

You might have for instance three tables:

PERSON
PersonId LastName FirstName

STUDENT StudentId PersonId

STAFF
StaffId PersonId

I have no idea exactly what the real usage, but it's not recommended to make use of inheritence only for reusability.

In the beginning sight, getting a Person class inside a college management system (such like) doesn't appear right.

It might be better you mention the aimOrobjective from the assignment - what's should really do.