Content Comparison

...

The tables in the Solution DB should be structured to efficiently support these queries. Here is an example schema:

...

Creating a Solution Database using the following query:

Expand

title	file_queue

Code Block

CREATE TABLE `file_queue` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PARTNER_NAME` varchar(50) DEFAULT NULL,
  `TRANSACTION_TYPE` varchar(50) DEFAULT NULL,
  `FILE_REF_ID` varchar(50) DEFAULT NULL,
  `PHASE` varchar(50) DEFAULT NULL,
  `OVERALL_STATUS` varchar(50) DEFAULT NULL,
  `ERROR_CODE` varchar(50) DEFAULT NULL,
  `INCOMING_FILE_DATE` datetime DEFAULT NULL,
  `TOTAL_RECORDS` int DEFAULT NULL,
  `DUPLICATE_RECORDS` int DEFAULT NULL,
  `UNIQUE_RECORDS` int DEFAULT NULL,
  `GROUP_ID` varchar(50) DEFAULT NULL,
  `V3_GROUP_ID` varchar(50) DEFAULT NULL,
  `V3_PHASE` varchar(50) DEFAULT NULL,
  `V3_STATUS` varchar(50) DEFAULT NULL,
  `V3_TOTAL_RECORDS` int DEFAULT NULL,
  `V3_GOOD_RECORDS` int DEFAULT NULL,
  `V3_ERROR_RECORDS` int DEFAULT NULL,
  `V3_PASS_PERCENTAGE` varchar(50) DEFAULT NULL,
  `V3_ERROR_MESSAGE` varchar(255) DEFAULT NULL,
  `CORE_PHASE` varchar(50) DEFAULT NULL,
  `CORE_STATUS` varchar(50) DEFAULT NULL,
  `CORE_TOTAL_RECORDS` int DEFAULT NULL,
  `CORE_GOOD_RECORDS` int DEFAULT NULL,
  `CORE_ERROR_RECORDS` int DEFAULT NULL,
  `CORE_PASS_PERCENTAGE` varchar(50) DEFAULT NULL,
  `CORE_ERROR_MESSAGE` varchar(255) DEFAULT NULL,
  `CREATED_BY` varchar(50) NOT NULL,
  `CREATED_DATE` datetime NOT NULL,
  `LAST_MODIFIED_BY` varchar(50) NOT NULL,
  `LAST_MODIFIED_DATE` datetime NOT NULL,
  `TRANSACTION_NAME` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ERROR_CODE` (`ERROR_CODE`),
  KEY `idx_file_queue_FILE_REF_ID` (`FILE_REF_ID`),
  CONSTRAINT `file_queue_ibfk_1` FOREIGN KEY (`ERROR_CODE`) REFERENCES `error_code` (`ERROR_CODE`)
) ENGINE=InnoDB AUTO_INCREMENT=1209564827 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand

title	file_name_details

CREATE TABLE `file_name_details` (

`ID` int NOT NULL AUTO_INCREMENT,

`FILE_QUEUE_ID` int NOT NULL,

`LOCATION_TYPE` varchar(50) NOT NULL,

`FILE_TYPE` varchar(50) NOT NULL,

`FILE_PATH` varchar(500) NOT NULL,

`FILE_NAME` varchar(400) DEFAULT NULL,

`CREATED_BY` varchar(50) NOT NULL,

`CREATED_DATE` datetime NOT NULL,

`LAST_MODIFIED_BY` varchar(50) NOT NULL,

`LAST_MODIFIED_DATE` datetime NOT NULL,

PRIMARY KEY (`ID`),

UNIQUE KEY `UC_FILE_ATT` (`FILE_QUEUE_ID`,`FILE_NAME`,`FILE_TYPE`,`LOCATION_TYPE`),

CONSTRAINT `file_name_details_ibfk_1` FOREIGN KEY (`FILE_QUEUE_ID`) REFERENCES `file_queue` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=7676 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand

title	error_code

CREATE TABLE `error_code` (

`ID` int NOT NULL AUTO_INCREMENT,

`ERROR_CODE` varchar(10) NOT NULL,

`ERROR_MESSAGE` varchar(255) NOT NULL,

`PHASE_ID` varchar(10) NOT NULL,

PRIMARY KEY (`ID`),

UNIQUE KEY `UC_ERROR_CD` (`ERROR_CODE`),

KEY `PHASE_ID` (`PHASE_ID`),

CONSTRAINT `error_code_ibfk_1` FOREIGN KEY (`PHASE_ID`) REFERENCES `phase` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand

title	application_data

CREATE TABLE `application_data` (

`ID` int NOT NULL AUTO_INCREMENT,

`FILE_QUEUE_ID` int NOT NULL,

`APPLICATION_TYPE` varchar(50) DEFAULT NULL,

`RECORD_STATUS` varchar(50) DEFAULT NULL,

`ERROR_MESSAGE` varchar(2000) DEFAULT NULL,

`WARNING_MESSAGE` varchar(2000) DEFAULT NULL,

`SSN` varchar(20) DEFAULT NULL,

`EMPLOYEE_ID` varchar(20) DEFAULT NULL,

`EMPLOYEE_FIRST_NAME` varchar(200) DEFAULT NULL,

`EMPLOYEE_LAST_NAME` varchar(200) DEFAULT NULL,

`COVERAGE_ID` varchar(50) DEFAULT NULL,

`PRODUCT_TYPE_CODE` varchar(50) DEFAULT NULL,

`GROUP_POLICY_NUMBER` varchar(50) DEFAULT NULL,

`BENEFIT_PLAN_IDENTIFIER` varchar(50) DEFAULT NULL,

`CREATED_BY` varchar(50) NOT NULL,

`CREATED_DATE` datetime NOT NULL,

`LAST_MODIFIED_BY` varchar(50) NOT NULL,

`LAST_MODIFIED_DATE` datetime NOT NULL,

`BENEFIT_CLASS_IDENTIFIER` varchar(50) DEFAULT NULL,

`MASTER_AGREEMENT_NUMBER` varchar(50) DEFAULT NULL,

`EMPLOYEE_MIDDLE_NAME` varchar(50) DEFAULT NULL,

`EMPLOYEE_SUFFIX_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_BIRTH_DATE` varchar(50) DEFAULT NULL,

`EMPLOYEE_GENDER_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_MARITAL_STATUS_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_HOME_PHONE` varchar(50) DEFAULT NULL,

`EMPLOYEE_EMAIL` varchar(50) DEFAULT NULL,

`EMPLOYEE_ALTERNATE_EMAIL` varchar(50) DEFAULT NULL,

`EMPLOYEE_MAILING_ADDRESS_FIRST_LINE_ADDRESS` varchar(100) DEFAULT NULL,

`EMPLOYEE_MAILING_ADDRESS_THIRD_LINE_ADDRESS` varchar(100) DEFAULT NULL,

`EMPLOYEE_MAILING_ADDRESS_CITY_NAME` varchar(50) DEFAULT NULL,

`EMPLOYEE_MAILING_ADDRESS_STATE_PROVINCE_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_MAILING_ADDRESS_POSTAL_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_MAILING_ADDRESS_COUNTRY_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_IDENTIFIER` varchar(50) DEFAULT NULL,

`EMPLOYEE_TOBACCO_USECODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_WORK_ADDRESS_STATE_PROVINCE_CODE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_EMPLOYMENT_STATUS_CODE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_EMPLOYMENT_TYPE_CODE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_ORIGINAL_HIRE_DATE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_MOST_RECENT_HIRE_DATE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_ADJUSTED_SERVICE_DATE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_TERMINATION_DATE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_JOB_TITLE_TEXT` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_OCCUPATION_TEXT` varchar(400) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_PAYROLL_FREQUENCY_QUANTITY` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_EXEMPT_CODE` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_UNION_INDICATOR` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_WORK_LOCATION_TEXT` varchar(50) DEFAULT NULL,

`EMPLOYMENT_INFORMATION_WORK_STATE_CODE` varchar(50) DEFAULT NULL,

`COVERAGE_BENEFIT_SUBCLASS_IDENTIFIER` varchar(50) DEFAULT NULL,

`COVERAGE_COVERAGERIDER_RIDEROPTION_IDENTIFIER` varchar(50) DEFAULT NULL,

`COVERAGE_BILL_GROUP_IDENTIFIER` varchar(50) DEFAULT NULL,

`COVERAGE_BILL_SUB_GROUP_IDENTIFIER` varchar(50) DEFAULT NULL,

`COVERAGE_TIER_CODE` varchar(50) DEFAULT NULL,

`COVERAGE_EFFECTIVE_DATE` varchar(50) DEFAULT NULL,

`COVERAGE_TERMINATION_DATE` varchar(50) DEFAULT NULL,

`COVERAGE_BENEFIT_AMOUNT` varchar(50) DEFAULT NULL,

`DEPENDENT_TOBACCO_USECODE` varchar(50) DEFAULT NULL,

`DEPENDENT_FIRST_NAME` varchar(50) DEFAULT NULL,

`DEPENDENT_LAST_NAME` varchar(50) DEFAULT NULL,

`DEPENDENT_BIRTH_DATE` varchar(50) DEFAULT NULL,

`DEPENDENT_GENDER_CODE` varchar(50) DEFAULT NULL,

`DEPENDENT_RELATIONSHIP_TYPE_CODE` varchar(50) DEFAULT NULL,

`DEPENDENT_BENEFIT_AMOUNT` varchar(50) DEFAULT NULL,

`EVENT_DATE` varchar(50) DEFAULT NULL,

`EVENT_TYPE_REASON_CODE` varchar(50) DEFAULT NULL,

`EVENT_TYPE_CODE` varchar(50) DEFAULT NULL,

`EMPLOYEE_POSITION` varchar(50) DEFAULT NULL,

`POLICY_PRODUCTID_CLASS_POSITION` varchar(50) DEFAULT NULL,

PRIMARY KEY (`ID`),

KEY `idx_file_queue_id` (`FILE_QUEUE_ID`),

CONSTRAINT `fk_grade_id` FOREIGN KEY (`FILE_QUEUE_ID`) REFERENCES `file_queue` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=426019 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand

title	phase

CREATE TABLE `phase` (

`ID` varchar(10) NOT NULL,

`PHASE_NAME` varchar(50) NOT NULL,

`CREATED_BY` varchar(50) NOT NULL,

`CREATED_DATE` datetime NOT NULL,

`LAST_MODIFIED_BY` varchar(50) NOT NULL,

`LAST_MODIFIED_DATE` datetime NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand

title	solution_configuration

CREATE TABLE `solution_configuration` (

`ID` int NOT NULL AUTO_INCREMENT,

`CATEGORY` varchar(50) NOT NULL,

`PROPERTY_NAME` varchar(50) NOT NULL,

`PROPERTY_DEFAULT` varchar(50) DEFAULT NULL,

`PROPERTY_VALUE` varchar(50) DEFAULT NULL,

`PROPERTY_ORDER` int DEFAULT NULL,

`ACTIVE_FLAG` varchar(10) NOT NULL,

`CREATED_BY` varchar(50) NOT NULL,

`CREATED_DATE` datetime NOT NULL,

`LAST_MODIFIED_BY` varchar(50) NOT NULL,

`LAST_MODIFIED_DATE` datetime NOT NULL,

`TRANSACTION_NAME` varchar(50) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand

title	transaction_run_history

CREATE TABLE `transaction_run_history` (

`ID` bigint NOT NULL AUTO_INCREMENT,

`transactionId` varchar(100) NOT NULL,

`transactionType` varchar(500) DEFAULT NULL,

`transactionName` varchar(500) DEFAULT NULL,

`partner` varchar(400) DEFAULT NULL,

`inputFile` varchar(500) DEFAULT NULL,

`inputFilePath` varchar(500) DEFAULT NULL,

`sourceSystemOutputFile` varchar(500) DEFAULT NULL,

`sourceSystemOutputFilePath` varchar(500) DEFAULT NULL,

`adeptiaOutputFile` varchar(500) DEFAULT NULL,

`adeptiaOutputFilePath` varchar(500) DEFAULT NULL,

`deltaReportFile` varchar(500) DEFAULT NULL,

`deltaReportFilePath` varchar(500) DEFAULT NULL,

`status` varchar(100) DEFAULT NULL,

`executedBy` varchar(500) DEFAULT NULL,

`executionDate` varchar(50) DEFAULT NULL,

`submittedBy` varchar(100) DEFAULT NULL,

`matchPercent` varchar(50) DEFAULT NULL,

`processInstanceID` varchar(50) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1705672808620 DEFAULT CHARSET=latin1;

2. Design Process flow

Overview

...

Version	Old Version 14	New Version 15
Changes made by	Deepak Kumar (Deactivated)	Deepak Kumar (Deactivated)
Saved on	Jun 21, 2024	Jun 21, 2024

Versions Compared

Key

2. Design Process flow

Overview