Multitenant Structure: coral_central_dev

Documentation of the database coral_central_dev:
CORAL Central Database.xlsx

Table scripts for PostgreSQL

STATE table:

-- Table: dbo.State

-- DROP TABLE IF EXISTS dbo."State";

CREATE TABLE IF NOT EXISTS dbo."State"
(
    "StateID" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "StateName" character varying(200) COLLATE pg_catalog."default" NOT NULL,
    "CreateByID" integer,
    "CreateTime" date,
    "UpdateByID" integer,
    "UpdateTime" date,
    CONSTRAINT "State_pkey" PRIMARY KEY ("StateID")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."State"
    OWNER to orms_db_user;

COMMENT ON COLUMN dbo."State"."StateID"
    IS 'Uniquely generated ID for a State';

COMMENT ON COLUMN dbo."State"."StateName"
    IS 'Name of the state, For displaying in the reports and UI.';

COUNTY table:

-- Table: dbo.County

-- DROP TABLE IF EXISTS dbo."County";

CREATE TABLE IF NOT EXISTS dbo."County"
(
    "CountyID" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "StateID" integer NOT NULL,
    "CountyName" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "CreateByID" integer,
    "CreateTime" timestamp without time zone,
    "UpdateByID" integer,
    "UpdateTime" timestamp without time zone,
    CONSTRAINT "County_pkey" PRIMARY KEY ("CountyID"),
    CONSTRAINT "FK_Coral_State_StateID" FOREIGN KEY ("StateID")
        REFERENCES dbo."State" ("StateID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."County"
    OWNER to orms_db_user;

COMMENT ON TABLE dbo."County"
    IS 'table to house all the counties of Coral';

COMMENT ON COLUMN dbo."County"."CountyID"
    IS 'system generated unique number for counties ';

COMMENT ON COLUMN dbo."County"."StateID"
    IS 'state to which the county belong to';

COMMENT ON COLUMN dbo."County"."CountyName"
    IS 'Name of the county, for displaying on reports and UI.';

TENANT table:

-- Table: dbo.Tenant

-- DROP TABLE IF EXISTS dbo."Tenant";

CREATE TABLE IF NOT EXISTS dbo."Tenant"
(
    "TenantID" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "CountyID" integer,
    "StateID" integer,
    "DBServer" character varying(255) COLLATE pg_catalog."default",
    "DBName" character varying(255) COLLATE pg_catalog."default",
    "DBLogin" character varying(100) COLLATE pg_catalog."default",
    "DBPassword" character varying(100) COLLATE pg_catalog."default",
    "CreateByID" integer,
    "CreateTime" timestamp without time zone,
    "UpdateByID" integer,
    "UpdateTime" timestamp without time zone,
    CONSTRAINT "Tenant_pkey" PRIMARY KEY ("TenantID"),
    CONSTRAINT "FK_Coral_County_CountyID" FOREIGN KEY ("CountyID")
        REFERENCES dbo."County" ("CountyID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Tenant"
    OWNER to orms_db_user;

COMMENT ON COLUMN dbo."Tenant"."TenantID"
    IS 'Uniquely generated ID for a Tenant';

MASTERUSER table:

-- Table: dbo.MasterUser

-- DROP TABLE IF EXISTS dbo."MasterUser";

CREATE TABLE IF NOT EXISTS dbo."MasterUser"
(
    "TenantUserID" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "TenantID" integer NOT NULL,
    "Email" character varying(500) COLLATE pg_catalog."default" NOT NULL,
    "CreateByID" integer,
    "CreateTime" timestamp without time zone,
    "UpdateByID" integer,
    "UpdateTime" timestamp without time zone,
    "IsActive" boolean DEFAULT true,
    CONSTRAINT "MasterUser_pkey" PRIMARY KEY ("TenantID", "Email"),
    CONSTRAINT "FK_Coral_Tenant_TenantID" FOREIGN KEY ("TenantID")
        REFERENCES dbo."Tenant" ("TenantID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."MasterUser"
    OWNER to orms_db_user;

COMMENT ON COLUMN dbo."MasterUser"."TenantUserID"
    IS 'This is unique column of the table. User table from the multitenant database ';

COMMENT ON COLUMN dbo."MasterUser"."TenantID"
    IS 'Assigned Tenant ID';

COMMENT ON COLUMN dbo."MasterUser"."Email"
    IS 'Email address used by the user to authenticate himself in SSO.';

Catalis Official Records

Multitenant Structure: coral_central_dev

Related content