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?
A user is not a set of credentials, because I might use multiple in the same web application (for example, sign in with username and password, or sign in via Google).
A user is not a person, because I might use multiple in the same web application for different purposes (for example, a personal one, and one for work).
A user is not an identity, because I might use multiple in the same web application for different purposes (for example, on SoundCloud, I might have multiple artist pseudonyms).
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:
- Credentials are used for authentication
- Identities (a.k.a. profiles) todo
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:
- I can have multiple credentials: I can sign in into my account in multiple ways: username and password, email address used for a magic link, Google sign-in, …)
- I can have multiple profiles (identities): I have my own soundcloud.com/denis page, but I can create other pages for other pseudonyms (I have no other ones at the moment, but I could have).
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.