In agreement with the comments made by @Laughing Vergil, this is the structure I would use:
CREATE TABLE users
(
user_id BIGINT PRIMARY KEY,
username CHARACTER VARYING(255) NOT NULL,
password CHARACTER VARYING(255) NOT NULL
) ;
CREATE TABLE services
(
service_id BIGINT PRIMARY KEY,
name CHARACTER VARYING(45) NOT NULL
) ;
CREATE TABLE connections
(
connection_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
service_id BIGINT NOT NULL REFERENCES services(service_id),
authorization_code CHARACTER VARYING(255),
refreh_token CHARACTER VARYING(255),
access_token CHARACTER VARYING(255),
service_username CHARACTER VARYING(255),
service_password CHARACTER VARYING(255),
/* Either one of authorization code or username must be null,
but not both */
CHECK ((authorization_code IS NULL) <> (service_username IS NULL)),
/* If the authorization_code is NULL;
refresh_token and access_token must also be null */
CHECK (
CASE WHEN (authorization_code IS NULL) THEN
(refreh_token IS NULL) AND
(authorization_code IS NULL)
ELSE
true
END),
/* If the service_username is NULL; service_password must also be null */
CHECK ((service_username IS NULL) = (service_password IS NULL))
) ;
You can do a few tests:
INSERT INTO users VALUES(1, 'user', 'pwd');
INSERT INTO services VALUES (1, 'service');
/* Good */
INSERT INTO connections (connection_id, user_id, service_id, service_username, service_password)
VALUES (10, 1, 1, 'user', 'pwd');
/* Also good */
INSERT INTO connections (connection_id, user_id, service_id, authorization_code, refreh_token, access_token)
VALUES (20, 1, 1, 'auth_code', NULL /* Don't know yet */, NULL /* Don't know yet */) ;
/* Bad */
INSERT INTO connections (connection_id, user_id, service_id, service_username, authorization_code)
VALUES (21, 1, 1, 'user', 'auth') ;
/* Bad */
INSERT INTO connections (connection_id, user_id, service_id, service_username, service_password)
VALUES (30, 1, 1, 'user', NULL);
connections
table. You may need a user field as well, if the user for the connection will be different from the user in theusers
table. Then set up rules - if password is not null, access_token must be null. If access_token is not null, password must be null. Finally, when making a connection, if access_token is null, useisNull(connections.user, users.username)
andconnections.password
. If both are null, generate an error. – Laughing Vergil Jan 5 '17 at 22:29