Question 234969 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to create a table that should only allow a single relation to exist out of many?

I have a table called Configuration:

CREATE TABLE "Configuration" 
(
    "id" CHAR(36) NOT NULL,
    "namespace" VARCHAR(255) NOT NULL,
    "key" VARCHAR(255) NOT NULL,
    "value" JSONB NOT NULL,
    "level" "ConfigLevel" NOT NULL,
    "userId" CHAR(36),
    "orgId" CHAR(36),
    "teamId" CHAR(36),
    "moduleId" CHAR(36),
    "moduleSubscriptionId" CHAR(36),
    "workflowId" CHAR(36),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Configuration_pkey" PRIMARY KEY ("id")
);

That also has a unique index:

CREATE UNIQUE INDEX "Configuration_namespace_key_level_userId_orgId_teamId_modul_key" 
ON "Configuration"("namespace", "key", "level", "userId", "orgId", "teamId", "moduleId", "moduleSubscriptionId", "workflowId");

My goal with this is to be able to save configurations for a given namespace, key and level and one of the possible relations (user, org, team, module, subscription or workflow).

I’m not happy with this setup though as if I add a new relation I need to modify the constraint and I also have to add individual fields for each relation (so all the others are always NULL).

Is there a database design pattern that I can apply here to make this easier to maintain and more forward-compatible?

Tags:

2

Answers


  1. 0

    From what I understnad, only a single column will be used for your relations

    if my understanding is correct, then a design with a single column for all the possible relation would be better

    CREATE TABLE "Configuration" 
(
    "id" CHAR(36) NOT NULL,
    "namespace" VARCHAR(255) NOT NULL,
    "key" VARCHAR(255) NOT NULL,
    "value" JSONB NOT NULL,
    "level" "ConfigLevel" NOT NULL,
    "RelationType" CHAR(50),
    "RelationValue" CHAR(36),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Configuration_pkey" PRIMARY KEY ("id")
);

    you will have more rows, but better performance

    You can later pivot this table to better fit other needs.

    Login or Signup to reply.
  2. 0

    Postgres provides functions num_nulls() and num_nonnulls(). Add a check constraint on the number of those columns that must/cannot be null.

    CREATE TABLE "Configuration" 
(
    "id" CHAR(36) NOT NULL,
    "namespace" VARCHAR(255) NOT NULL,
    "key" VARCHAR(255) NOT NULL,
    "value" JSONB NOT NULL,
    "level" "ConfigLevel" NOT NULL,
    "userId" CHAR(36),
    "orgId" CHAR(36),
    "teamId" CHAR(36),
    "moduleId" CHAR(36),
    "moduleSubscriptionId" CHAR(36),
    "workflowId" CHAR(36),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Configuration_pkey" PRIMARY KEY ("id")
  , constraint just_one_value_ck check( num_nulls("userId"  
                                                 ,"orgId"  
                                                 ,"teamId"  
                                                 ,"moduleId"
                                                 ) = 1
                                      )  
);

    You would be much better off using VARCHAR or TEXT instead of CHAR data type. See Don’t Do This:Text Storage

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search