Database Design – Should User and User Profile Be in Different Tables?

databasedatabase-design

I have seen in a couple of projects that developers prefer to keep essential user info in one table (email/login, password hash, screen name) and rest of the non essential user profile in another (creation date, country, etc). By non-essential I mean that this data is needed only occasionally. Obvious benefit is that if you are using ORM querying less fields is obviously good. But then you can have two entities mapped to same table and this will save you from querying stuff you don't need (while being more convenient). Does anybody know any other advantage of keeping these things in two tables?

Best Answer

It depends on the size and requirements of your project.

I can see one way in which data about users can be divided into two sets, with different purposes and thus requirements:

  • Identity data: user name, password hash, email address, last login time, etc.
  • User profile data, which includes users preferences, latest activity, status updates etc.

Note that there are some attributes about the user that can fall in either category (e.g. user's date of birth). The difference between these two sets though is that the first one is tightly controlled and only through certain workflows can it be modified. For example, changing a password may require providing existing password, changing the email may require verification of email, and it would be used in the case the user forgets the password.

Preferences do not require such ACLs, and could theoretically be modified by the user or another application so long as user consents to it. The stakes are low if an application maliciously or due to a bug corrupts the data or attempts to modify it (assuming other security measures are taken.) However, it would usually be disastrous if any of the user name, password or email could be modified since they can either be used to assume user's identity or deny service or cause support costs etc. for the admin.

Thus, usually the data is stored in two types of systems:

  • Identity data will typically go in a directory or an IAM solution.
  • Preference data will end up in a database.

Having said that, in practice, people will violate these rules and use one or the other (e.g. SQL server behind ASP.NET membership provider).

As the identity data becomes larger or the organization that uses it becomes larger, different types of problems creep in. For example, in the case of directory, it will attempt replicate the password changes immediately to all servers in a multi-server environment. However, user preference only require eventual consistency. (FYI: Both of these are different optimizations of CAPS theorem.)

Finally, directories (esp. the online/cloud directories) will also issue access tokens for other resources using protocols such as OAUTH (e.g. consider Facebook, Google, Microsoft Account, ADFS), whereas a database has no such need. A database will support quite complex joins and query structure, which directory does not need.

For more details, a few searches on identity directory vs database would help.

It eventually comes down to what your scenarios are and expected to be in the future, including integration with any third parties (and what they are using). If it's a well-contained project and you're confident that you can secure the user identity data and authenticate correctly, then you could go for database. Otherwise, it might be worth investigating an identity directory.

If you go for DB, IMO, using one DB vs two would eventually come down to access control, both for users and applications.

Related Topic