Here’s a suggestion how our accounts database might look like. Note that I’m not a database expert, so feel free to take it apart without mercy.
Note that this does not contain profile data (other than the nickname); I think the profile data (including an optional publicly shown email, that may be different from the account recovery email) is better stored together with the site contents.
I separated account and identity so that we have the option to later implement several identities per account if we decide so (which I personally hope). In MVP, there would be one identity entry per account entry.
Note that the only time the account table would be accessed is at login, password recovery, or when sending account-related mails. Everything else could be done purely on the identity table. Therefore I don’t think the separate tables are a performance disadvantage even if we don’t end up supporting multiple identities.
One thing that might be a bad choice is that I made separate tables for each login method. My idea being that I suspect it’s more easy to add a table than to add fields to the accounts table. The disadvantage is that the link between authentication type and authentication data table is not in the database, but only in the software. Maybe a database expert can tell me whether this is a good or bad idea.
A note on the “private” vs. ”public” label in the comments:
Any table labelled ”private” contains information that is only ever given to the account owner and, where required, site operators/moderators. Any table labelled “public” contains information publicly displayed on the site (like the nickname). I tried to avoid mixing public and private data in the same table; however the account field in the identity table is actually private data. But making a separate table for account/identity seemed to be overkill to me; maybe my judgement here was wrong.
Anyway, here is my suggestion:
-- table of accounts: personally identifiable, private
CREATE TABLE accounts (
id SERIAL PRIMARY KEY NOT NULL, -- account Id, used only internally, never appears in the interface
email TEXT NOT NULL, -- email used for things like password resets
status INT NOT NULL -- active, suspended, deleted, ...
)
-- table of identities: personally identifiable, public
CREATE TABLE identities (
id SERIAL PRIMARY KEY NOT NULL, -- identity Id, used by the site
account INT NOT NULL, -- the account this identity belongs to
nickname TEXT NOT NULL -- the name associated with this identity
)
-- table of authentication methods: personally identifiable, private
CREATE TABLE authenticationMethods (
accountId INT, -- the account the identification depends on
authenticationType INT -- e.g. 1 = password login, 2 = StackExchange
authenticationDataId INT -- the entry in the corresponding table
)
-- data needed for password authentication (except for email address, as that is already in accounts):
-- not personally identifiable, private
CREATE TABLE passwordAuth (
id SERIAL PRIMARY KEY NOT NULL,
salt TEXT,
passwordHash TEXT
)
-- data needed for login with SE: personally identifiable, private
CREATE TABLE stackAuth (
id SERIAL PRIMARY KEY NOT NULL,
stackAuthData TEXT -- replace by whatever data SE authentication requires
)
-- session data: personally identifiable, private
CREATE TABLE sessions {
cookieData TEXT PRIMARY KEY NOT NULL, -- the contents of the session cookie
identity INT, -- the identity of the session
expires TIMESTAMP -- the expiry date of the session
}
