Performance Management — Data Model & Entity Relationships

This document answers one question in depth: how do the tables across SkillManagement, TMS, PERM, and CMGM relate to each other? The confusion arises because the word "Competency" is used in two different modules with different meanings — and because CMGM borrows tables from SkillManagement rather than owning them. This guide maps every table, every FK, and shows a concrete sample of data flowing end-to-end through all four modules.

SkillManagement — skill catalog & proficiency levels TMS — training programmes & training competencies PERM — appraisal KRAs & evaluation scores CMGM — competency frameworks, gap analysis, dev plans

The Key Confusion — MSKILL vs MCOMPETENCY

Both MSKILL and MCOMPETENCY exist. They are different tables in different modules representing different concepts. Neither is "missing" — they serve parallel purposes that will eventually be linked.

📚 MSKILL (SkillManagement module)

  • What it is: A discrete, assessable work capability with a structured 1–5 proficiency scale
  • Examples: "SQL Proficiency", "Presentation Skills", "Leadership", "Conflict Resolution"
  • Proficiency levels: Defined in MSKILLLEVEL — each level has a label and a behavioural descriptor
  • Who uses it: CMGM (for gap analysis), Work Instructions, ESS self-assessment
  • CMGM role: This is the unit of measurement in CMGM — gaps, profiles, dev plans, anchors, and career path requirements all reference SKILLID

🎓 MCOMPETENCY (TMS module)

  • What it is: A training-level competency — what a learning programme is designed to build
  • Examples: "Communication & Presentation", "Data & Analytics", "Leadership & Team Development"
  • Proficiency levels: MAXLEVELNO column only — no separate level-descriptor table
  • Who uses it: TMS only — linked to training programmes via MPROGCOMPETENCYMAP
  • CMGM role: Does not directly drive CMGM gap analysis. Is a parallel concept — the "training output" that will be linked to CMGM gaps in a future integration
⚠ Why two separate "competency" concepts?
MSKILL is granular and multi-levelled — designed for precise employee assessment (Level 3 = Competent, Level 5 = Expert with specific observable behaviours). MCOMPETENCY is coarser — designed to tag what a training programme addresses (e.g. "this course builds Communication & Presentation competency up to Level 3"). The two share MSKILLGROUP and MSKILLDOMAIN as classification references, but serve different purposes. Future integration: when CMGM raises a Critical gap for SkillId=2 (SQL Proficiency), the system will look up TMS programmes where MPROGCOMPETENCYMAP covers competencies in the same domain/group — and surface them as recommended training.
DimensionMSKILL (SkillManagement)MCOMPETENCY (TMS)
Owner moduleSkillManagementTMS
Primary keySKILLIDCOMPETENCYID
Proficiency scaleDetailed — child table MSKILLLEVEL with label + descriptor per levelSimple — MAXLEVELNO column only, no level descriptor table
Behavioral anchorsYes — MCOMPETENCYANCHOR in CMGM stores observable indicators per Skill×LevelNo — TMS competencies have no anchor table
Used by CMGM gap engineYes — MCOMPETENCYKRAMAP.SkillId, MCAREERPATHREQUIREMENT.SkillId, TSKILLDEVPLAN.SkillIdNo — CMGM never references COMPETENCYID
Linked to training programmesNot yet (planned)Yes — MPROGCOMPETENCYMAP.COMPETENCYID → MTRAININGPROGRAMME
ClassificationVia MSKILLGROUP, MSKILLDOMAINVia same MSKILLGROUP, MSKILLDOMAIN (shared)
Employee proficiency storedIn MEMPLOYEESKILL (SkillManagement), also derived in TEMPLOYEECOMPETENCYPROFILE.SkillGapsJson (CMGM)Not stored per employee

Cross-Module Zone Map

This diagram shows which tables live in which module, and which cross-module foreign key relationships exist. Solid arrows = FK constraint. Dashed arrows = business trigger (no DB-level FK — data flows via BLL logic).

SKILLMANAGEMENT MODULE MSKILLGROUP PK GROUPID GROUPNAME, TENANTID ISACTIVE, SORTORDER MSKILLDOMAIN PK DOMAINID DOMAINNAME, TENANTID ISACTIVE MSKILL ★ (shared anchor) PK SKILLID FK GROUPID → MSKILLGROUP FK DOMAINID → MSKILLDOMAIN SKILLNAME, SKILLCODE, LEVELCOUNT ISACTIVE, TENANTID → Referenced by CMGM, WI, ESS MSKILL LEVEL PK SLEVELID FK SKILLID SLNO (1-5) LEVELLABEL LEVELDESCRIPTOR MEMPLOYEESKILL PK SKILLPROFILEID | FK EMPLOYEEID → MEMPLOYEE | FK SKILLID → MSKILL CURRENTLEVEL (1-5) | ASSESSEDBY | ASSESSEDON | TENANTID Employee's actual skill proficiency — updated via SkillManagement UI or ESS self-rate CMGM reads this table; does not write to it TMS (TRAINING MANAGEMENT) MODULE MCOMPETENCY PK COMPETENCYID FK DOMAINID → MSKILLDOMAIN (shared) FK GROUPID → MSKILLGROUP (shared) COMPETENCYNAME, COMPETENCYTYPE (0=Functional/1=Behavioral/2=Technical) MAXLEVELNO, ISACTIVE, TENANTID ⚠ Does NOT drive CMGM gap analysis — see MSKILL for that MTRAINING PROGRAMME PK PROGRAMMEID PROGRAMMENAME DURATION, STATUS MPROGCOMPETENCY MAP PK PROGCOMPETENCYMAPID FK PROGRAMMEID FK COMPETENCYID → MCOMPETENCY CONTRIBUTESTOLEVELNO CREDITWEIGHT, ISDIRECTEVIDENCE ⟳ PLANNED FUTURE INTEGRATION CMGM gap (SkillId=X) will search MPROGCOMPETENCYMAP for programmes covering competencies in the same SkillDomain/Group → suggest training NOT YET IMPLEMENTED — linkage is via BLL logic, not a DB FK PERM (PERFORMANCE MANAGEMENT) MODULE MKRA PK KRAId KRAName, KRACode CategoryId, Weight AppraisalPlanId Referenced by MCOMPETENCYKRAMAP TAPPRAISALEVAL PK AppraisalEvalId | FK EmployeeId → MEMPLOYEE | FK KRAId → MKRA FK AppraisalId → TAPPRAISAL | EvaluatorType (SELF/MANAGER/PEER) ManagerScore, SelfScore, FinalScore | Comments | Status (Pending/Completed) After cycle FINALISE → CompetencyProfileBLL reads FinalScore per KRA per Employee → looks up MCOMPETENCYKRAMAP → derives skill proficiency → writes TEMPLOYEECOMPETENCYPROFILE CMGM (COMPETENCY MANAGEMENT) MODULE MCOMPETENCY FRAMEWORK PK FrameworkId Code, Name, Description Status (Active/Inactive) Container for KRA→Skill mappings MCOMPETENCY KRAMAP PK CompetencyKRAMapId FK FrameworkId → MCOMPETENCYFRAMEWORK FK KRAId → MKRA (PERM) FK SkillId → MSKILL (SkillMgmt) ExpectedProficiencyLevel (1-5) IsMandatory → drives gap severity MCOMPETENCY ANCHOR PK AnchorId FK SkillId → MSKILL (SkillMgmt) ProficiencyLevel (1-5) AnchorText (observable behaviour) BehaviorIndicatorsJson Unique: (SkillId, Level, TenantId) MCAREERPATH PK CareerPathId FK FrameworkId → MCOMPETENCYFRAMEWORK CurrentRoleId, TargetRoleId (→MDESIGNATION) TypicalMonthsRequired PathCode, PathName, Status MCAREERPATH REQUIREMENT PK CareerPathReqId FK CareerPathId → MCAREERPATH FK SkillId → MSKILL (SkillMgmt) MinProficiencyLevel, IsMandatory TEMPLOYEECOMPETENCYPROFILE PK CompetencyProfileId | FK EmployeeId → MEMPLOYEE | FK FrameworkId → MCOMPETENCYFRAMEWORK FK CareerPathId → MCAREERPATH | TargetRoleId | GeneratedOn | Status (0=Draft/1=Published) OverallCompetencyScore (DECIMAL) | Notes SkillGapsJson — [{SkillId, SkillName, CurrentLevel, RequiredLevel, Gap, IsMandatory, Severity}] ONE ROW per employee per framework per cycle. All skill gaps inside JSON — NOT one row per skill. Generated by: CompetencyProfileBLL triggered after TAPPRAISALEVAL cycle is FINALISED TSKILLDEVPLAN PK DevPlanId PlanType (0=Ind/1=Team/2=Dept) TargetId (EmployeeId / TeamId) FK SkillId → MSKILL (SkillMgmt) CurrentProficiencyLevel (1-5) TargetProficiencyLevel (1-5) Priority (0=Low…3=Critical) TrainingMethod (0-4) TargetDate, EstimatedHours TrainerId, CompletionNotes Status (0=Planned…3=OnHold) Manually created per gap by manager or employee Can be individual, team, or department scoped Cross-module FK → MSKILL TMS → SkillManagement shared ref BLL trigger (no DB FK) Intra-CMGM FK

Complete Foreign Key Reference

From TableColumn→ To TableColumnModuleType
MSKILLGROUPIDMSKILLGROUPGROUPIDSkillMgmtDB FK
MSKILLDOMAINIDMSKILLDOMAINDOMAINIDSkillMgmtDB FK
MSKILLLEVELSKILLIDMSKILLSKILLIDSkillMgmtDB FK
MEMPLOYEESKILLSKILLIDMSKILLSKILLIDSkillMgmtDB FK
MCOMPETENCYDOMAINIDMSKILLDOMAINDOMAINIDTMS → SMDB FK cross-module
MCOMPETENCYGROUPIDMSKILLGROUPGROUPIDTMS → SMDB FK cross-module
MPROGCOMPETENCYMAPPROGRAMMEIDMTRAININGPROGRAMMEPROGRAMMEIDTMSDB FK
MPROGCOMPETENCYMAPCOMPETENCYIDMCOMPETENCYCOMPETENCYIDTMSDB FK
TAPPRAISALEVALKRAIdMKRAKRAIdPERMDB FK
TAPPRAISALEVALEmployeeIdMEMPLOYEEEMPLOYEEIDPERMDB FK
MCOMPETENCYKRAMAPFrameworkIdMCOMPETENCYFRAMEWORKFrameworkIdCMGMDB FK
MCOMPETENCYKRAMAPKRAIdMKRAKRAIdCMGM → PERMDB FK cross-module
MCOMPETENCYKRAMAPSkillIdMSKILLSKILLIDCMGM → SMDB FK cross-module
MCOMPETENCYANCHORSkillIdMSKILLSKILLIDCMGM → SMDB FK cross-module
MCAREERPATHFrameworkIdMCOMPETENCYFRAMEWORKFrameworkIdCMGMDB FK
MCAREERPATHCurrentRoleIdMDESIGNATIONDESIGNATIONIDCMGM → HR CoreDB FK
MCAREERPATHREQUIREMENTCareerPathIdMCAREERPATHCareerPathIdCMGMDB FK
MCAREERPATHREQUIREMENTSkillIdMSKILLSKILLIDCMGM → SMDB FK cross-module
TEMPLOYEECOMPETENCYPROFILEEmployeeIdMEMPLOYEEEMPLOYEEIDCMGMDB FK
TEMPLOYEECOMPETENCYPROFILEFrameworkIdMCOMPETENCYFRAMEWORKFrameworkIdCMGMDB FK
TSKILLDEVPLANSkillIdMSKILLSKILLIDCMGM → SMDB FK cross-module
TAPPRAISALEVAL (PERM)—TEMPLOYEECOMPETENCYPROFILE (CMGM)—PERM → CMGMBLL trigger only — no DB FK. CompetencyProfileBLL is called by AppraisalFinalizedSubscriber (Dapr event)

End-to-End Sample Data Journey

Follow one employee — Arun Kumar, Junior Software Engineer — through the complete flow. Each step shows the actual rows written to or read from the database.

1
HR creates Skills in SkillManagement
SkillManagement Tables: MSKILL + MSKILLLEVEL
SKILLIDSKILLCODESKILLNAMELEVELCOUNTTENANTID
1SK-001Presentation Skills51001
2SK-002SQL Proficiency51001
7SK-007Project Planning51001
Each skill also has 5 MSKILLLEVEL rows (SLNO 1–5) with LEVELLABEL and LEVELDESCRIPTOR. These are written in SkillManagement — CMGM reads them but never writes them.
↓
2
HR creates a Competency Framework in CMGM
CMGM Table: MCOMPETENCYFRAMEWORK
FrameworkIdCodeNameStatusTENANTID
10FW-SESoftware Engineer Framework1 (Active)1001
↓
3
HR maps KRAs (from PERM) → Skills (from SkillManagement)
CMGM Table: MCOMPETENCYKRAMAP — this is the bridge between PERM and SkillManagement
MapIdFrameworkIdKRAIdKRANameSkillIdSkillNameExpectedLevelIsMandatory
110301Technical Delivery2SQL Proficiency41 (Yes)
210301Technical Delivery7Project Planning31 (Yes)
310302Client Communication1Presentation Skills41 (Yes)
Reading this: "For employees in the Software Engineer Framework — when their Technical Delivery KRA scores well, we expect them to be at Level 4 in SQL Proficiency and Level 3 in Project Planning."
↓
4
Manager evaluates Arun Kumar in the PERM appraisal cycle
PERM Table: TAPPRAISALEVAL — KRA scores per employee
EvalIdEmployeeIdEmployeeNameKRAIdKRANameManagerScoreFinalScoreEvaluatorType
50011001Arun Kumar301Technical Delivery2.82.8MANAGER
50021001Arun Kumar302Client Communication4.24.2MANAGER
↓ HR clicks "Finalise Cycle" → Dapr event fires → AppraisalFinalizedSubscriber → CompetencyProfileBLL
5
CompetencyProfileBLL derives skill proficiency from KRA scores
CMGM BLL calculation — reads TAPPRAISALEVAL + MCOMPETENCYKRAMAP → computes per-skill level
Derivation formula: DerivedLevel = ROUND(FinalScore × 5 ÷ 5) = ROUND(FinalScore) mapped to 1–5 scale

KRA "Technical Delivery" FinalScore = 2.8
  → SQL Proficiency DerivedLevel = ROUND(2.8) = 3   | Required = 4 | Gap = 4 − 3 = +1 (Mandatory → HIGH)
  → Project Planning DerivedLevel = ROUND(2.8) = 3   | Required = 3 | Gap = 3 − 3 = 0 (MET)

KRA "Client Communication" FinalScore = 4.2
  → Presentation Skills DerivedLevel = ROUND(4.2) = 4   | Required = 4 | Gap = 4 − 4 = 0 (MET)
↓ BLL writes one row to TEMPLOYEECOMPETENCYPROFILE
6
TEMPLOYEECOMPETENCYPROFILE row written for Arun Kumar
CMGM Table: TEMPLOYEECOMPETENCYPROFILE — ONE row per employee per framework per cycle
ProfileIdEmployeeIdFrameworkIdGeneratedOnOverallScoreStatus
1001001 (Arun Kumar)10 (SW Eng Framework)2026-04-1558.501 (Published)
SkillGapsJson column (shown expanded — stored as JSON array in DB):
SkillIdSkillNameCurrentLevelRequiredLevelGapIsMandatorySeverity
2SQL Proficiency34+1YesHIGH
7Project Planning330YesMET
1Presentation Skills440YesMET
↓ Manager reviews gap → creates dev plan
7
Manager creates a Skill Development Plan for the HIGH gap
CMGM Table: TSKILLDEVPLAN — manually created per gap, references MSKILL (SkillManagement)
DevPlanIdTargetIdSkillIdSkillNameCurrentLevelTargetLevelMethodTargetDateStatus
2001001 (Arun)2SQL Proficiency34Online (2)2026-09-30InProgress
TSKILLDEVPLAN.SkillId = 2 → foreign key to MSKILL.SKILLID = 2 (in SkillManagement). The dev plan "owns" nothing about the skill — it just points to it.
↓ (future) TMS integration — search MPROGCOMPETENCYMAP for courses that develop SQL Proficiency
8
(Future) TMS surfaces relevant training programmes
TMS Tables: MCOMPETENCY + MPROGCOMPETENCYMAP — planned integration, not yet live
CompetencyIdCompetencyNameProgrammeIdProgrammeNameContributesToLevelNo
102Data & Analytics2002SQL Foundations Certification3
102Data & Analytics2005Advanced Query Optimisation4
The link "SQL Proficiency (SkillId=2) → Data & Analytics Competency (CompetencyId=102)" does not yet exist as a DB FK. The planned integration adds a MSKILL_COMPETENCY_MAP bridge table or a MCOMPETENCY.RelatedSkillId column so CMGM gaps can query TMS programmes automatically.

Gap Severity Calculation — Full Reference

Gap severity is computed in CompetencyProfileBLL after deriving each skill level. No separate table stores severity — it is calculated at runtime and embedded in SkillGapsJson.

Gap ValueIsMandatorySeverityColourAction Required
≤ −1 (Actual > Required)EitherEXCEEDSGreenConsider for mentoring or stretch assignment
0 (Actual = Required)EitherMETGreenMaintain current level
1No (Optional)MEDIUMYellowDevelopment plan recommended this cycle
1Yes (Mandatory)HIGHOrangeDevelopment plan required this cycle
≥ 2Yes (Mandatory)CRITICALRedImmediate intervention — plan + escalation to HR
ⓘ IsMandatory source: MCOMPETENCYKRAMAP.IsMandatory — set by HR when mapping KRAs to skills. If a skill appears in multiple KRA mappings with different IsMandatory values, the most restrictive (mandatory) wins during gap calculation. The ExpectedProficiencyLevel used is the highest required level across all KRAs that map to that skill.

Sample: Three employees, same framework, different gap outcomes

EmployeeSkillRequired LevelDerived Level (from KRA score)GapMandatorySeverity
Arun KumarSQL Proficiency43+1YesHIGH
Project Planning330YesMET
Presentation Skills440YesMET
Priya SharmaSQL Proficiency42+2YesCRITICAL
Project Planning32+1YesHIGH
Presentation Skills45−1YesEXCEEDS
Rajan MenonSQL Proficiency440YesMET
Project Planning34−1YesEXCEEDS
Presentation Skills43+1YesHIGH