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.';
, multiple selections available,
Related content
Database: coral_dev
Database: coral_dev
More like this
Empty Tenant Database Scripts
Empty Tenant Database Scripts
Read with this
Database Schema
Database Schema
More like this
Extensions Coral VS2022
Extensions Coral VS2022
Read with this
Database: coral_erecord
Database: coral_erecord
More like this
Identity Server Integration - Create Users
Identity Server Integration - Create Users
Read with this