2

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

  • Is it always a service rule, or can be a combination of service/user? – McNets Jan 3 '17 at 16:08
  • I do not really understand what you mean? Do you asking that it always needs to be a connection between a user and a service? – Robbert Jan 4 '17 at 10:14
  • I'm sorry, I mean, it there any service where one user connects using OAuth and another user, for the same service, connects by User/Pass)? – McNets Jan 4 '17 at 10:26
  • @McNets No I don't think so. It's for some services that use OAuth and some User/Pass. – Robbert Jan 5 '17 at 8:20
  • 1
    In that case, I would say that you need a password field added to the connections table. You may need a user field as well, if the user for the connection will be different from the user in the users 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, use isNull(connections.user, users.username) and connections.password. If both are null, generate an error. – Laughing Vergil Jan 5 '17 at 22:29
0
+100

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);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.