Mysql – Storing multiple types of users and their respective credentials in separate or same table

database-designinnodbMySQL

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:

  1. doing so would allow us to reuse some of our DB queries for all authentication, forgotpassword, tokens and related functionality.
  2. Reduce the number of joins necessary
  3. 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:

  1. that it adds unnecessary complexity to the DB design
  2. the students and sponsors dont share enough commonality in functionality.
  3. 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.
  4. 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:

Users and Pass in Same Table?

Users and Pass in Same Table? Part 2

Multiple types of users, different tables?

Best Answer

This question appears to me to be related to another one, concerning generalization/specialization (aka inheritance) vs. association. The "baking" approach is one of the strategies to map inheritance to relational databases (table per concrete class in Hibernate). The separate table approach can also be regarded as such a strategy (table per subclass), or as a mapping of associated, not inherited, objects.

By the way, talking of inheritance, your students and sponsors have quite a bit in common, so they might be generalized to person, which could then also carry the credentials.

That said, let me comment on your criteria. The criteria against don't appear particularly strong to me:

  1. Complexity: About the same in both solutions. Having two instead of three tables still means to have one piece of functionality around credentials which needs to be applied to both students and sponsors.

  2. Commonality: No matter whether the rest of student and sponsor data and functions have much in common, authenticating users certainly is a significant bit of commonality.

  3. Transferring the credentials part to another platform won't get easier if it is included in the student or sponor table.

  4. This will only be significant if there are lots of such queries, or if they are very intricate. Even then you can simulate the separate table by an SQL union, and, the other way round, the "baked" tables by joins.

Unfortunately, the critera for creating the new table aren't compelling either:

  1. Cf. criterium 4 above.

  2. Separating person from credentials appears to me even to introduce a join (if needed) between the two, not to save any joins.

  3. The amount of data, for those few attributes of all involved objecs, should not be a problem.

To sum up, neither of both solutions will break your neck. I have still a favor for the separate table (and, in particular, for separate program constructs to handle credentials), because access to a system is a separate concern. That's why holders of access to a system are sometimes not called users but principals. E.g. there might be credentials for web or rest services of a system that are not bound to any person, or credentials for automatic jobs operating on your data. As you said, granting or refusing access has not much to do with the attributes of a person, like name or age.

Related Question