Core_Dependent

Purpose

The Core.Dependent table stores comprehensive information about employee dependents and spouses. It tracks demographic details, relationship types, benefit eligibility, and coverage information for each dependent. This table is essential for benefits administration, dependent verification, and compliance reporting.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
DependentID	INT	NO	Primary key, unique identifier	DEPENDENTS	DEPENDENT_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	DEPENDENTS	EMPL_ID	Direct mapping
FirstName	VARCHAR(50)	NO	Dependent's first name	DEPENDENTS	FIRST_NAME	Direct mapping
LastName	VARCHAR(50)	NO	Dependent's last name	DEPENDENTS	LAST_NAME	Direct mapping
MiddleName	VARCHAR(50)	YES	Dependent's middle name	DEPENDENTS	MIDDLE_NAME	Direct mapping
BirthDate	DATE	NO	Dependent's date of birth	DEPENDENTS	BIRTH_DATE	Direct mapping
Gender	VARCHAR(10)	NO	Dependent's gender	DEPENDENTS	GENDER	Mapped from codes
RelationshipType	VARCHAR(20)	NO	Relationship to employee	DEPENDENTS	RELATIONSHIP	Mapped from codes
IsSpouse	BIT	NO	Spouse indicator	DEPENDENTS	IS_SPOUSE	Derived
IsChild	BIT	NO	Child indicator	DEPENDENTS	IS_CHILD	Derived
IsDisabled	BIT	NO	Disability indicator	DEPENDENTS	IS_DISABLED	Direct mapping
IsStudent	BIT	NO	Student status indicator	DEPENDENTS	IS_STUDENT	Direct mapping
StudentEndDate	DATE	YES	Student status end date	DEPENDENTS	STUDENT_END_DATE	Direct mapping
SSN	VARCHAR(11)	YES	Social Security Number	DEPENDENTS	SSN	Direct mapping
BenefitEligible	BIT	NO	Benefit eligibility indicator	DEPENDENTS	BENEFIT_ELIGIBLE	Derived
CoverageStartDate	DATE	YES	Coverage start date	DEPENDENTS	COVERAGE_START	Direct mapping
CoverageEndDate	DATE	YES	Coverage end date	DEPENDENTS	COVERAGE_END	Direct mapping
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: DEPENDENTS Table

Contains dependent information
Updated when dependent details change
Key fields mapped directly to Core.Dependent

Secondary Sources:

BENEFIT_ENROLLMENTS Table
- Dependent benefit coverage
- Used for eligibility validation
- Updated when enrollments change
STUDENT_STATUS Table
- Student verification information
- Used for student status validation
- Updated when student status changes

Data Transformation Rules

Relationship Type Mapping

Source Code -> Target Type
'SP' -> 'Spouse'
'CH' -> 'Child'
'DP' -> 'Domestic Partner'
'OT' -> 'Other'

Gender Mapping

Source Code -> Target Gender
'M' -> 'Male'
'F' -> 'Female'
'O' -> 'Other'

Spouse/Child Derivation

IsSpouse = CASE
  WHEN RelationshipType = 'Spouse' THEN 1
  ELSE 0
END

IsChild = CASE
  WHEN RelationshipType = 'Child' THEN 1
  ELSE 0
END

Benefit Eligibility Derivation

BenefitEligible = CASE
  WHEN CoverageStartDate IS NOT NULL AND
       (CoverageEndDate IS NULL OR CoverageEndDate > GETDATE()) THEN 1
  ELSE 0
END

Data Quality Rules

Required Fields
- DependentID
- EmployeeID
- FirstName
- LastName
- BirthDate
- Gender
- RelationshipType
- IsSpouse
- IsChild
- BenefitEligible
Validation Rules
- BirthDate must be valid
- StudentEndDate must be after BirthDate if present
- CoverageEndDate must be after CoverageStartDate if present
- SSN must be valid if present
- Gender must be valid
Business Rules
- Only one spouse per employee
- Child must be under age 26 unless disabled
- Student status must be verified annually
- Coverage dates must align with benefit periods
- SSN must be unique across dependents

Usage Notes

Reporting Considerations
- Use RelationshipType for dependent analysis
- Consider BenefitEligible for coverage reporting
- Track dependent changes over time
- Monitor student status
Performance Considerations
- Indexed on DependentID, EmployeeID, and RelationshipType
- Consider filtering on BenefitEligible for current dependents
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Core.Employee via EmployeeID
- Links to Benefits.Enrollment via DependentID
- Used in benefits administration views
- Source for dependent verification

Maintenance

Refresh Schedule
- Daily updates for dependent changes
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical dependents maintained in table
- Use CoverageEndDate for historical filtering
- Dependent history tracked for compliance
Monitoring
- Track dependent changes by employee
- Monitor eligibility validation
- Alert on validation rule violations
- Track student status verification

Core.Dependent Table Documentation