I've already read these posts:
Storing whether a user is a student or teacher? Is a lookup table necessary?
Creating a database for a scenario involving students, teachers and courses
In a school database, do I need to have repeating data when a student can also be a teacher?
All of them were close, but none are exactly what I want. A few points:
- This app will be used by Grade School teachers.
- A student will never be a teacher, and a teacher will never be a student.
- In addition to Students and Teachers, there will also be contact info for Admins who are neither students nor teachers.
- Teachers will always be Users. Admins may or may not be users. (We may store an Admin's name and contact info, but that admin may never have a reason or need to log into the app.)
My thought is to create a "Person" table:
- PersonId
- FirstName
- LastName
- Email (NULL if student)
- PersonType (Enum: 1 = Admin/Teacher, 2 = Student)
- Other Columns as needed
And another table "Passwords":
- PasswordId
- PersonId
- PasswordHash
- Other Columns as needed
If a Person is also a user, they will have a value for 'Email' and also a record in the Password table.
Am I on the right track?
Best Answer
So it depends (as most things go). What it'll mostly dependent on is how similar are your
Teachers
andStudents
objects. If they have a high similarity in attributes (very similar columns) then they likely can live in the same table. If they have more variation between the two objects, then likely you'll be better off normalizing the two entities into their own tables.From your basic description of the application, my guess would be you'll find (if not upfront, over time) that the
Teachers
andStudents
entities vary enough that it makes sense to split them up into their own tables.Further normalizing the attributes that they do share into a
Person
table is somewhat subjective, and for you to decide, but not a bad idea by any means for storing generic information like Email, Phone, and Address, etc.The one thing I'd be careful with over-normalizing is the
Passwords
table, as that reduces natural obscurity that comes with it being denormalized and may slightly open your vector of attack up in your database design. (E.g. if someone unauthorized gained access to your database, having a table calledPasswords
automatically shows that person probably the most important table they'd want to compromise.)