So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design.
In our case, we have two different types of accounts - students and sponsors.
The students have the following attributes associated: id, firstname, lastname, gender, university, colelgeyear, major, ethnicity, housing, etc.
The sponsors have the following: id, name, businesstype, firstname,lastname, position, phone, address1, address2, etc.
So there's a reasonable difference in the type of functionality that the two accounts are involved with and the type of information we store about them. The question however has been brought up of storing account credentials i.e. hashedpasword, salt, etc. I know that ideally we would not store this on the same DB and access only via API. But, let's say that we have to in this case for proof of concept reasons.
Should we create a new general account table that contains the following: id, email, hashedpassword, salt, resetpasswordexpires, tokens, verification, role (this determines whether an account is for a student or a sponsor).
The argument for creating this new table so far has been the following:
- doing so would allow us to reuse some of our DB queries for all authentication, forgotpassword, tokens and related functionality.
- Reduce the number of joins necessary
- Reduce the amount of data being queried. E.g. you don't need to know a student's college to reset his/her password.
The argument against creating this new table and instead baking the credentials into the students table and sponsors table has been the following:
- that it adds unnecessary complexity to the DB design
- the students and sponsors dont share enough commonality in functionality.
- That eventually saving passwords offsite would allow us to easily delete a column the sponsors table and students table and then proceed as we usually would.
- That DB Queries have to be written anyways to eventually find out from the accounts table what type of account was authenticated
Here are some related links that I have found but haven't quite answered, and in particular have never addressed the code-reuse argument: