Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- The below database I believe can handle multiple accounts per user with ease.
-- As a user, you can have an account with abc@example.com as email and a hashed password
-- You can also link your account via Facebook with the same email abc@example.com or
-- a different one such as xyz@example.com

CREATE TABLE IF NOT EXISTS authentication_types (
authentication_type_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
authentication_type_name VARCHAR NOT NULL,
PRIMARY KEY(authentication_type_id),
UNIQUE(authentication_type_name)
);

INSERT INTO authentication_types VALUES
(1, 'password'), 
(2, 'facebook'), 
(3, 'google'), 
(4, 'github'), 
(5, 'twitter');

-- The user has one or more accounts
-- The user can have only one account of a given type
-- Example: only 1 Facebook account and 1 Google account
-- If you feel this is restrictive let me know a better way

CREATE TABLE IF NOT EXISTS users (
user_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
enabled BOOLEAN NOT NULL DEFAULT TRUE, 
PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS accounts (
account_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
email VARCHAR NOT NULL, 
password VARCHAR, 
email_verified BOOLEAN NOT NULL DEFAULT FALSE, 
nickname VARCHAR, 
picture_url VARCHAR, 
is_primary_email BOOLEAN NOT NULL DEFAULT FALSE,
authentication_type_id INTEGER NOT NULL,
external_login_id VARCHAR,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY(account_id),
UNIQUE(email),
UNIQUE(authentication_type_id, email),
UNIQUE(authentication_type_id, external_login_id),
FOREIGN KEY (authentication_type_id) REFERENCES authentication_types (authentication_type_id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- the users with authentication_type_id as password will actually have a password
-- If we say email is unique, it becomes problematic
-- What if you have the same email on your Facebook and Google account?
-- So instead we say that the combination of authentication_type_id and email is unique
-- external_login_id is nothing but the unique login ID assigned by Twitter, Github etc
-- There is nothing to say that they are truly unique
-- It is possible that the Facebook ID for a user may be the same as the Pinterest ID for another user
-- So instead we say that the combination of authentication_type_id and external_login_id is unique

CREATE TABLE IF NOT EXISTS user_accounts (
user_id INTEGER NOT NULL,
account_id INTEGER NOT NULL,
PRIMARY KEY(user_id, account_id),
FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- user A with only password based account

INSERT INTO accounts(account_id, email, password, email_verified, nickname, picture_url, is_primary_email, authentication_type_id, external_login_id) VALUES (
1, 
'abc@example.com', 
'$2b$11$oHR4Tdcy8Mse1lB5Hmgj5O3u3SPgqolHRgBEVXvzLt5BjS8ujGXKS',
false,
null,
null,
true,
1,
null
);

INSERT INTO users VALUES(1, true);
INSERT INTO user_accounts VALUES(1, 1);

-- user B with password and facebook account

INSERT INTO accounts(account_id, email, password, email_verified, nickname, picture_url, is_primary_email, authentication_type_id, external_login_id) VALUES (
2, 
'bcd@example.com', 
'$2b$11$oHR4Tdcy8Mse1lB5Hmgj5O3u3SPgqolHRgBEVXvzLt5BjS8ujGXKS',
false,
null,
null,
true,
1,
null
);

INSERT INTO accounts(account_id, email, password, email_verified, nickname, picture_url, is_primary_email, authentication_type_id, external_login_id) VALUES (
3, 
'xyz@example.com', 
null,
true,
null,
null,
false,
1,
'hjdigodgjaigfshg123461'
);

INSERT INTO users VALUES(2, true);
INSERT INTO user_accounts VALUES(2, 2);
INSERT INTO user_accounts VALUES(2, 3);

SELECT * FROM accounts;
SELECT * FROM users;
SELECT * FROM user_accounts;

5 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

account_id	email	password	email_verified	nickname	picture_url	is_primary_email	authentication_type_id	external_login_id	created_at	updated_at
1	abc@example.com	$2b$11$oHR4Tdcy8Mse1lB5Hmgj5O3u3SPgqolHRgBEVXvzLt5BjS8ujGXKS	f	null	null	t	1	null	2022-06-20 03:37:40.290321+01	2022-06-20 03:37:40.290321+01
2	bcd@example.com	$2b$11$oHR4Tdcy8Mse1lB5Hmgj5O3u3SPgqolHRgBEVXvzLt5BjS8ujGXKS	f	null	null	t	1	null	2022-06-20 03:37:40.290321+01	2022-06-20 03:37:40.290321+01
3	xyz@example.com	null	t	null	null	f	1	hjdigodgjaigfshg123461	2022-06-20 03:37:40.290321+01	2022-06-20 03:37:40.290321+01

user_id	enabled
1	t
2	t

user_id	account_id
1	1
2	2
2	3