/
Multitenant Structure: coral_central_dev

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.';

 

Add label

Related content