Phase 3 – Common Master Template Structure

The Common Master template used in Jupyter Notebook contains two operational sheets based on the columns you shared:

1. Department & Designation Master Sheet

2. Complaint Type Master Sheet

Both sheets must be filled out before uploading in Phase-3.

Common Master Template: Common Master

1. Department & Designation Master Sheet

Columns Present:

Column Name	Description	Mandatory	Example
Department Name*	Name of the department	Yes	WATER DEPARTMENT

Example Rows (from your data)

Department Name*	Designation Name*
WATER DEPARTMENT	engineer

Notes

• Designations can be multiple under the same department.

• No empty department rows are allowed between entries.

2. Complaint Type Master Sheet

Columns Present:

Column Name	Description	Mandatory	Example
Complaint Type*	Main complaint category	Yes	Water not coming
Complaint Sub Type*	Sub-category of complaint	Yes	tap broken
Department Name*	Must match Department Name from Sheet 1	Yes	WATER DEPARTMENT
Resolution Time (Hours)*	SLA in hours	Yes	1
Search Words (comma separated)	Keywords used for search & auto-classification	Yes	water tab,water supply

Example Rows (from your data)

Complaint Type*	Complaint sub type*	Department Name*	Resolution Time (Hours)*	Search Words (comma separated)
Water not coming	tab broken	WATER DEPARTMENT	1	water tab,water supply

Notes

• Every complaint must map to an existing Department Name from Sheet 1.

• SLA* must be a numeric value (integer).

• Search words must be comma-separated without special characters.

• If a department has no complaints, leave it out entirely (do not add empty rows).

#add the datatype of table attributes

3. Relationship Between Both Sheets

The template enforces the following relationships:

Rule	Description
1	Complaint Type Sheet → Department Name must exist in Department Sheet
2	Departments must be created before designations and complaints
3	Designation Sheet uses the department name exactly as written in Department Sheet
4	All mandatory fields must be filled; blanks cause schema rejection

This ensures consistency across all master data.

---

4. Validation Rules in Jupyter Notebook

Excel-Level Validations

• Mandatory field enforcement

• Text-only validation

• Dropdown validation for Department Name

• Numeric validation for Resolution Time (Hours)

• Comma-separated keyword rules

MDMS Schema Validation

• The department must exist before designation

• The department must exist before the complaint

• Duplicate department or designation is rejected

• Invalid SLA values are rejected

• Complaint category and subtype uniqueness are enforced

API-Level Validation

• Department creation

• Designation creation

• Complaint type creation

• Duplicate detection

• Relationship enforcement