DB design - suggestions. Normalize

Question

I'm trying to improve the database design of my web system. Basically I have some doubts about a table that I have where I store the data of all my users, like FirstName, LastName, etc. This table stores the same data for all my different users, on this case for Students, Profesors and two more type of roles. Right now I have it as one single table call it PERSON. There I have all the relations of my foreign keys, even between "Persons" when a Student have a Profesor as Tutor. But I use some colums only when the Person is a Student, fields Enrolled or PaidUp for example and I keep it as null when is other type of role.

Now, my question is what are the cons and pros to divide the table Person, and create subclasses and store only the foreign key where I need it. Should I keep the single table and use "where" to filter my query, or do I normalize the table in several tables doing a join on Person when I need the general data?

Answer 1

A person is a person and should have its own table. A student or professor is a role played by a person. A person can play many roles. I was a student at my university, then I later taught there.

A student is enrolled in a class, so have an tacit relationship with a professor in that way. You could also track relationships explicitly, as below

Read up on Table Inheritance too

You design should look like this:

PERSON
id
name
dob
...


PERSON_ROLE
person_id references PERSON(id)
type {student, prof, etc}
student_number (nullable)
salary (nullable)
...


PERSON_RELATIONSHIPS
from_person_id references PERSON(id)
to_person_id references PERSON(id)
type {tutorOf, studentOf, ...}
...