0

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?

3
  • Consider how you'll query the database. Write the queries for different sections of your applicatoin. This should make it obvious if there is a single Person table or if Teachers/Students are distinct entities for your application. Its also unclear why a separate passwords table exist. Are you supporting multiple passwords per person?
    – danblack
    Jan 4, 2021 at 22:43
  • 1
    I want to support password history. If a user changes their password, previous passwords should not be re-usable. Jan 4, 2021 at 23:00
  • This would require exclusive subtypes, handled at length here: stackoverflow.com/questions/4763141/…
    – bbaird
    Jan 5, 2021 at 4:47

2 Answers 2

0

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

4
  • Thanks, JD. Question then about storing password histories (so that a single password can't be re-used)... what is the best-practice way to do this? Jan 4, 2021 at 23:49
  • @CaseyCrookston No problem! That's a good question, and maybe a little bit more focused for the Security.StackExchange.com. Unfortunately that's a little bit outside the scope of my expertise as a DBA, but from my limited knowledge I would say if you do need a Password history table, then using more obscure names like AccountHistory for the table may be a better choice. Additionally, storing only the last few password hashes helps reduce the amount of potentially sensitive data being stored at any given time.
    – J.D.
    Jan 4, 2021 at 23:57
  • This article may provide a little more guidance on best practices with designing a Password history table too.
    – J.D.
    Jan 4, 2021 at 23:57
  • 1
    Based on your answer, I just broke them out into separate tables :) Jan 5, 2021 at 4:50
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.