A “users” table is a smell

One of the first tables that gets created when a new web application is set up, is a “users” table, to contain all the users that can sign in to the web application.

What is a “user” though?

The concept of “user” is vague and conflates credentials and identities. As an alternative, consider having separate credentials and identities tables, and an accounts table to link them together:

The accounts table connects credentials and identities: one account (e.g. my personal account) can have multiple credentials and multiple identities associated with it.

Note that an account does not correspond to a person either. I might have two accounts (a personal one and one for work) with separate sets of credentials and separate identities.


As an example, imagine SoundCloud with separate credentials and identities concepts:

Furthermore, a single SoundCloud profile (a.k.a identity) could have multiple accounts linked to it. Imagine a SoundCloud profile for a band, that all band members can access.

Note last edited November 2023.
Backlinks: Perpetual interests.