Database: coral_dev
Juan José Orozco L. (Unlicensed)
Juan Fernando-Ramirez (Unlicensed)
Owned by Juan José Orozco L. (Unlicensed)
Documentation of the database coral_dev:
CORAL Dev - Database.xlsx
Table scripts for PostgreSQL
-- Table: dbo.DocumentType
-- DROP TABLE IF EXISTS dbo."DocumentType";
CREATE TABLE IF NOT EXISTS dbo."DocumentType"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Code" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"Description" character varying(255) COLLATE pg_catalog."default",
"Active" boolean DEFAULT true,
"HideFromPublic" boolean,
"Confidential" boolean,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "DocumentType_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."DocumentType"
OWNER to orms_db_user;
-- Table: dbo.Fee
-- DROP TABLE IF EXISTS dbo."Fee";
CREATE TABLE IF NOT EXISTS dbo."Fee"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"Description" character varying(150) COLLATE pg_catalog."default",
"Rate" numeric(19,4),
"Active" boolean DEFAULT true,
"IsTax" boolean DEFAULT false,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL,
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Fee_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Fee"
OWNER to orms_db_user;
-- Table: dbo.DocumentTypeFee
-- DROP TABLE IF EXISTS dbo."DocumentTypeFee";
CREATE TABLE IF NOT EXISTS dbo."DocumentTypeFee"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"DocumentTypeId" bigint NOT NULL,
"FeeId" bigint NOT NULL,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL,
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "DocumentTypeFee_pkey" PRIMARY KEY ("Id"),
CONSTRAINT "DocumentType_DocumentTypeId_fkey" FOREIGN KEY ("DocumentTypeId")
REFERENCES dbo."DocumentType" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT "Fee_FeeId_fkey" FOREIGN KEY ("FeeId")
REFERENCES dbo."Fee" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."DocumentTypeFee"
OWNER to orms_db_user;
-- Table: dbo.Audit
-- DROP TABLE IF EXISTS dbo."Audit";
CREATE TABLE IF NOT EXISTS dbo."Audit"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"TableName" character varying(100) COLLATE pg_catalog."default",
"TableId" bigint,
"Action" character varying(50) COLLATE pg_catalog."default",
"OldValue" text COLLATE pg_catalog."default",
"NewValue" text COLLATE pg_catalog."default",
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL,
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
CONSTRAINT "Audit_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Audit"
OWNER to orms_db_user;
-- Table: dbo.Agent
-- DROP TABLE IF EXISTS dbo."Agent";
CREATE TABLE IF NOT EXISTS dbo."Agent"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Name" character varying(255) COLLATE pg_catalog."default" NOT NULL,
"Address1" character varying(255) COLLATE pg_catalog."default",
"Address2" character varying(255) COLLATE pg_catalog."default",
"City" character varying(100) COLLATE pg_catalog."default",
"State" character varying(100) COLLATE pg_catalog."default",
"Zip" character varying(50) COLLATE pg_catalog."default",
"Phone" character varying(50) COLLATE pg_catalog."default",
"Email" character varying(50) COLLATE pg_catalog."default",
"Active" boolean DEFAULT true,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Agent_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Agent"
OWNER to orms_db_user;
-- Table: dbo.RoleType
-- DROP TABLE IF EXISTS dbo."RoleType";
CREATE TABLE IF NOT EXISTS dbo."RoleType"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "RoleType_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."RoleType"
OWNER to orms_db_user;
-- Table: dbo.Role
-- DROP TABLE IF EXISTS dbo."Role";
CREATE TABLE IF NOT EXISTS dbo."Role"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"RoleTypeId" bigint NOT NULL,
"Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"Description" character varying(255) COLLATE pg_catalog."default",
"Active" boolean DEFAULT true,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Role_pkey" PRIMARY KEY ("Id"),
CONSTRAINT "RoleType_RoleTypeId_fkey" FOREIGN KEY ("RoleTypeId")
REFERENCES dbo."RoleType" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Role"
OWNER to orms_db_user;
-- Table: dbo.Right
-- DROP TABLE IF EXISTS dbo."Right";
CREATE TABLE IF NOT EXISTS dbo."Right"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"Description" character varying(255) COLLATE pg_catalog."default",
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Right_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Right"
OWNER to orms_db_user;
-- Table: dbo.RoleRight
-- DROP TABLE IF EXISTS dbo."RoleRight";
CREATE TABLE IF NOT EXISTS dbo."RoleRight"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"RoleId" bigint NOT NULL,
"RightId" bigint NOT NULL,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "RoleRight_pkey" PRIMARY KEY ("Id"),
CONSTRAINT "Right_RightId_fkey" FOREIGN KEY ("RightId")
REFERENCES dbo."Right" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT "Role_RoleId_fkey" FOREIGN KEY ("RoleId")
REFERENCES dbo."Role" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."RoleRight"
OWNER to orms_db_user;
Location
-- Table: dbo.Location
-- DROP TABLE IF EXISTS dbo."Location";
CREATE TABLE IF NOT EXISTS dbo."Location"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"Active" boolean DEFAULT true,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Location_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Location"
OWNER to orms_db_user;
INSERT INTO dbo."Location"(
"Name", "Active", "CreateByUserId")
VALUES ('Test Location', true, 123);
Source
-- Table: dbo.Source
-- DROP TABLE IF EXISTS dbo."Source";
CREATE TABLE IF NOT EXISTS dbo."Source"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"Active" boolean DEFAULT true,
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Source_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Source"
OWNER to orms_db_user;
INSERT INTO dbo."Source"("Name", "CreateByUserId")
VALUES ('eRecording', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
VALUES ('Over the Counter', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
VALUES ('United States Postal Service', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
VALUES ('UPS', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
VALUES ('Fedex', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
VALUES ('Third Party Mail Delivery Service', 123);
Transaction
-- Table: dbo.Transaction
-- DROP TABLE IF EXISTS dbo."Transaction";
CREATE TABLE IF NOT EXISTS dbo."Transaction"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"LocationId" bigint NOT NULL,
"SourceId" bigint NOT NULL,
"Active" boolean DEFAULT true,
"Status" character varying(50) COLLATE pg_catalog."default",
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
CONSTRAINT "Transaction_pkey" PRIMARY KEY ("Id"),
CONSTRAINT "Location_LocationId_fkey" FOREIGN KEY ("LocationId")
REFERENCES dbo."Location" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "Source_SourceId_fkey" FOREIGN KEY ("SourceId")
REFERENCES dbo."Source" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."TransactionStatus"
OWNER to orms_db_user;
, multiple selections available,
Add label
Related content
Multitenant Structure: coral_central_dev
Multitenant Structure: coral_central_dev
More like this
Empty Tenant Database Scripts
Empty Tenant Database Scripts
Read with this
Database Schema
Database Schema
More like this
Technologies Propossed
Technologies Propossed
Read with this
Database: coral_erecord
Database: coral_erecord
More like this
Database Environments
Database Environments
Read with this