Creating single table vs creating multiple table

Question

I have lots of services which I am integrating. (also I am new to sql)

For each service integration I get different response,

For example, for zoom, I would just need to store the access_Token, for github, I would need to store the access_token and organization_idand asana would have something like "workspace_gid" and token.

Should we create a seperate table for each service asana, github, jira or is it better to have single table with clauses? Can someone help me with pros and cons of having single table vs multi-table?

Multiple Table

With Multiple Table, I can enforce the columns to have value when saving into Db.

create table "public"."service_asana" ( "id" bigint generated by default as identity not null, "created_at" timestamp with time zone default now(), "workspace_id" bigint not null, "token" text not null, "workspace_gid" text not null default ''::text, "auto_invite" boolean not null default false, "last_synced" timestamp with time zone, "sync_status" service_sync_status ); create table "public"."service_docusign" ( "id" bigint generated by default as identity not null, "created_at" timestamp with time zone default now(), "workspace_id" bigint not null, "token" text not null, "org" text not null default ''::text, "account_id" text not null, "permission_id" text not null, "auto_invite" boolean not null default false, "last_synced" timestamp with time zone, "sync_status" service_sync_status ); 

but things like permission_id and account_id, I am never going to sort or query things on them

Single Table,

The other approach I was thinking about is to store service data in column as in JSON format.

Which one is more recommended. Also, in single Table, even auth token should be inside JSON? because I am never gonna query on that. Alternatively, Is there any better approach?

Answer 1

Often people will choose to store service credentials in a crypted vault and inject them via text file or env vars. But I will just assume that an RDBMS is perfect for your needs.

---

store service [credentials] in a column as JSON format

Yes.

One row per service. It's an all-or-nothing arrangement, since there's no use case for extracting just a subset of the credential headers.

The multiple table arrangement seems nightmarish. Suppose your InfoSec staff want to verify that 90-day password rotations have been regularly happening, or they need to deal right away with this morning's breach. Better to examine update timestamps across all services with a single-table query, than trying to track down all relevant tables and hoping the created_at column name is spelled identically in each.

---

With Multiple Table, I can enforce the columns to have value when saving into Db.

We mostly rely on referential integrity to preserve complex invariants on how entities relate to one another, in a way that will "keep the app honest" even if it has buggy code. Here, we don't really care about details of the one or three headers that a given service requires. All that matters is "granted permission!" versus "denied!"

So write some code which automates the permission checking and will INSERT only valid credentials into your table. Furthermore, it should be easy to run automated tests which validate that rows are still good, they still tell the truth. For example, if GitHub issues a PAT token that worked fine Monday but turns out to be expired come Tuesday, your test suite should be able to quickly bring that to your attention. You will also want to support "expiring in the next seven days" reports that let you avoid embarrassing lapses in coverage.

Expired / invalid credentials are caused by things outside the RDBMS's control, such as the passage of time or failure to pay a hosting bill. So referential integrity doesn't have much of a role to play here. Each automated check will need to communicate with the given service.

Answer 2

Do have multiple tables; don't have multiple tables the way you've shown.

Make a top-level service table with all of the columns that are common to all of your services. Make child service tables (asana, docusign, etc.) that hold a key that's both primary and foreign to the parent key (and not generated), as well as columns specific to that service. This is a normalized design, and neither of the designs in the original post are normalized. I do not see JSON being useful here.