clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805509 fiddles created (40741 in the last week).

CREATE TABLE AuthorizationSourceType ( AuthSourceTypeCd CHAR(1) NOT NULL ,Name VARCHAR(50) NOT NULL ,CONSTRAINT PK_AuthorizationSourceType PRIMARY KEY (AuthSourceTypeCd) ,CONSTRAINT AK_AuthorizationSourceType UNIQUE (Name) )
 hidden batch(es)


INSERT INTO AuthorizationSourceType VALUES ('I','Internal') ,('E','External')
2 rows affected
 hidden batch(es)


CREATE TABLE AuthorizationSource ( AuthSourceCd CHAR(4) NOT NULL ,AuthSourceTypeCd CHAR(1) NOT NULL ,Name VARCHAR(50) NOT NULL ,CONSTRAINT FK_AuthorizationSource_Categorized_As_AuthorizationSourceType FOREIGN KEY (AuthSourceTypeCd) REFERENCES AuthorizationSourceType (AuthSourceTypeCd) ,CONSTRAINT PK_AuthorizationSource PRIMARY KEY (AuthSourceCd) ,CONSTRAINT AK_AuthorizationSource UNIQUE (Name) )
 hidden batch(es)


INSERT INTO AuthorizationSource VALUES ('EML','I','Email') ,('BIRD','E','BirdApp') ,('FACE','E','Facepage') ,('HOOL','E','Hooli')
4 rows affected
 hidden batch(es)


CREATE TABLE AppUser ( UserId INT NOT NULL ,CONSTRAINT PK_User PRIMARY KEY (UserId) )
 hidden batch(es)


CREATE TABLE UserAuthorization ( UserId INT NOT NULL ,AuthSourceCd CHAR(4) NOT NULL ,CONSTRAINT FK_UserAuthorization_For_User FOREIGN KEY (UserId) REFERENCES AppUser (UserId) ,CONSTRAINT FK_UserAuthorization_Uses_AuthorizationSource FOREIGN KEY (AuthSourceCd) REFERENCES AuthorizationSource (AuthSourceCd) ,CONSTRAINT PK_UserAuthorization PRIMARY KEY (UserId, AuthSourceCd) )
 hidden batch(es)


CREATE TABLE UserEmailAuthorization ( UserId INT NOT NULL ,AuthSourceCd CHAR(4) NOT NULL ,Email VARCHAR(250) NOT NULL /* encrypted password could go here, possibly better in other table w/salt */ ,CONSTRAINT FK_UserEmail_Used_For_UserAuthorization FOREIGN KEY (UserId, AuthSourceCd) REFERENCES UserAuthorization (UserId, AuthSourceCd) ,CONSTRAINT PK_UserEmailAuthorization PRIMARY KEY (UserId) ,CONSTRAINT AK_UserEmailAuthorization UNIQUE (Email) ,CONSTRAINT CK_AuthSourceCd_Is_Email CHECK (AuthSourceCd = 'EML') )
 hidden batch(es)


CREATE TABLE UserExternalAuthorization ( UserId INT NOT NULL ,AuthSourceCd CHAR(4) NOT NULL ,ExternalLoginId VARCHAR(50) NOT NULL /* Not sure of datatype here */ ,CONSTRAINT FK_UserExternalAuthorization_Is_UserAuthorization FOREIGN KEY (UserId, AuthSourceCd) REFERENCES UserAuthorization (UserId, AuthSourceCd) ,CONSTRAINT PK_UserExternalAuthorization PRIMARY KEY (UserId, AuthSourceCd) ,CONSTRAINT AK_UserExternalAuthorization UNIQUE (AuthSourceCd, ExternalLoginId) ) /* Needs trigger to reject insert if the AuthorizationSourceType is not external */
 hidden batch(es)


CREATE TABLE Login ( UserId INT NOT NULL ,LoginTs TIMESTAMP(6) NOT NULL ,AuthSourceCd CHAR(4) NOT NULL ,Email VARCHAR(250) NOT NULL /* This is an audit value - email entered or returned by external auth*/ ,CONSTRAINT FK_Login_With_UserAuthorization FOREIGN KEY (UserId, AuthSourceCd) REFERENCES UserAuthorization (UserId, AuthSourceCd) ,CONSTRAINT PK_Login PRIMARY KEY (UserId, LoginTs) )
 hidden batch(es)


/* Create user with email */ INSERT INTO AppUser VALUES (1); INSERT INTO UserAuthorization VALUES(1,'EML'); INSERT INTO UserEmailAuthorization VALUES (1,'EML','jkl@mnopqrs.com');
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


/* Create user with Hooli login */ INSERT INTO AppUser VALUES (2); INSERT INTO UserAuthorization VALUES (2,'HOOL'); INSERT INTO UserExternalAuthorization VALUES (2,'HOOL','2trlsk83@%RTSddfjsjkdkjs');
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


/* Add Facepage login to user 1 */ INSERT INTO UserAuthorization VALUES(1,'FACE'); INSERT INTO UserExternalAuthorization VALUES (1,'FACE','982346nkdsrWWW^YTYghdd');
1 rows affected
1 rows affected
 hidden batch(es)


/* User 1 logs in with email */ INSERT INTO Login VALUES (1,NOW(),'EML','jkl@mnopqrs.com')
1 rows affected
 hidden batch(es)


/* User 2 logs in with Hooli */ INSERT INTO Login VALUES (2,NOW(),'HOOL','abc@defghijk.net')
1 rows affected
 hidden batch(es)


/* User 1 logs in with Facepage (returns different email) */ INSERT INTO Login VALUES (1,NOW(),'FACE','jkl_work@bidness.com')
1 rows affected
 hidden batch(es)


SELECT * FROM Login
userid logints authsourcecd email
1 2021-11-17 20:24:50.034921 EML jkl@mnopqrs.com
2 2021-11-17 20:24:50.037044 HOOL abc@defghijk.net
1 2021-11-17 20:24:50.038044 FACE jkl_work@bidness.com
 hidden batch(es)