Keep user and user profile in different tables?

Question

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?

Answer 1

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.

Answer 2

There are, at least, three cases when having a person table for basic attributes and a second table for other attributes with a one-to-one relationship, is desirable:

• BLOB data like a picture. A separate table allows data to be stored separately for performance reasons, for example in a separate tablespace.
• Data that doesn't apply to everybody or that only apply to a person playing a certain role. Think of it as columns that would be null in many rows if they were part of the main table. In the database of a clinic, you can have a person table, a patient table and a medic table, in the first one you would have basic attributes, in the second one only attributes pertinent when the person is a patient like insurance coverage, in the third table (when the person is a medic) you could have the medical-specialty and other data that only applies to medical personel. Obviously a medic can be a patient.
• A table that materializes a relationship with an entity in a remote system. In this case the table establishes an equivalence between unique identifiers in separate database for interoperability reasons.

I think the case you expose fits within the second case.

Answer 3

My main reason to keep them separate is to try and avoid what's known in Object Oriented programming as a 'god class'. ORM's relate tables and fields to classes and attributes, so it becomes relevant as the SQL level too (even without an ORM there's a similar principle at play generally).

The User class (and by association the user table) is frequently the table that becomes the god class with hundred of attributes/fields, dozens or hundreds of methods and a class definition (for the methods) of over 1000 lines. I've seen it all. More than once.

So the separation out of user tries to address that. There may be person, user, account and the separation of concerns may seem a bit artificial but it is to try and avoid complexity and ensure that each object is only concerned about 1 aspect of the data.