Database Design – Structuring User Service Connection

database-design

I am working on a SQL database structure for an application that connects users to different API's of services. So one user can connect to for example 2 API's and the data of one service will be used inside the other service via the user.

I have drawn a simple database structure with a user, service and connection table. My idea is to make the connection by storing the user_id and service_id in the connection table together with the credentials.

The problem is that some services use OAuth (so based on access tokens) and some use a username and password. I don't know how to arrange my connections table to provide both options.

enter image description here

Best Answer

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);
Related Question