Question :
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?
Answer :
So it depends (as most things go). What it’ll mostly dependent on is how similar are your Teachers and Students 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 and Students 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 called Passwords automatically shows that person probably the most important table they’d want to compromise.)
Yes, but the primary key should also include PersonType.
Then in each sub-table (Teachers, Students etc) you can create the same primary key which is also a foreign key on Person. This represents a 1 -> 0..1 relationship. The PersonType field in this table should be constrained to a single value