📑 Migration Documentation: 20230713184636_AddorUpdateTablesforRBAC.cs


Table of Contents

  1. Overview & Purpose
  2. Migration Workflow
  3. Schema Renames & Column Adjustments
  4. Extending the Employee Entity
  5. Enriching ODS Staging Tables
  6. New Tables for Role-Based Access Control (RBAC)
  7. Indexes & Foreign Keys
  8. Rollback (Down Method)
  9. Entity-Relationship Diagram (ERD)
  10. Usage & Best Practices
  11. Conclusion

🧐 Overview & Purpose

This EF Core Migration augments the database schema to support:

  • A Role-Based Access Control (RBAC) system
  • Department assignments for employees
  • Expanded ODS (Operational Data Store) staging tables for Jobs & Employees
  • Additional ODS Customer data
  • Renaming legacy staging tables & key columns for consistency

Context
Irby Imaging’s data platform ingests raw HR & job data into staging tables (ODS_Jobs_Staging, ODS_Employee_Staging). This migration shapes that data model for production use and introduces RBAC tables to manage system permissions.


🔄 Migration Workflow

  1. Drop obsolete foreign keys, primary keys & unique constraints
  2. Rename tables and columns for naming consistency
  3. Alter existing columns (e.g., make IDs non-nullable)
  4. Add dozens of new metadata columns to:
    • Employee
    • ODSJobs (formerly ODS_Jobs_Staging)
    • ODSEmployees (formerly ODS_Employee_Staging)
  5. Create new RBAC & helper tables:
    • Department, Permissions, Role, LinkRolePermission, LinkUserRole, ODSCustomers
  6. Define indexes & foreign keys to enforce relationships
  7. Provide a Down method to revert all changes

🔁 Schema Renames & Column Adjustments

Operation Old New
Rename Table irby.ODS_Jobs_Staging ODSJobs
Rename Table irby.ODS_Employee_Staging ODSEmployees
Rename Column (Employee) CurrentJobId JobId
Rename Column (PKs) Employee.Id, Job.Id → EmployeeId, JobIdTimeSheetEntry.Id → TimeSheetEntryId >

Column Nullability Changes

  • TimeSheetEntry.EmployeeId & TimeEntryTypeId become non-nullable (Guid) with default Guid.Empty.

👤 Extending the Employee Entity

A suite of new employee attributes allows richer HR data:

Column Name Data Type Description
Birthday datetime2 Employee date of birth
StreetAddress, City nvarchar(max) Home address details
State, County, Zip nvarchar(max) Geographic metadata
EmploymentStartDate datetime2 Hire date
LastJob, LastJobDate nvarchar(max), datetime2 Prior role & date
RehireDate datetime2 Date of rehire if applicable
PayType, FLSAExemption nvarchar(max) Compensation & labor status
TerminatedDate datetime2 Date of termination
DepartmentId uniqueidentifier FK to Department table
RoleId uniqueidentifier FK to Role table

Note: DepartmentId & RoleId are optional, support organizing employees into teams & system roles.


🔄 Enriching ODS Staging Tables

ODSJobs (formerly ODS_Jobs_Staging)

  • Changed ORG_BU type from fixed-length key to free-form nvarchar(max).
  • Added 70+ business, geographical, contract, project, and customer metadata fields (e.g., ADDRESS_CITY, CLIENT_FUNDING_CODE, JOB_STATUS_DESC, PROJECT_ESTIMATOR, LAST_UPDATED).

ODSEmployees (formerly ODS_Employee_Staging)

  • Changed:
    • ORG_BU & EMPLOYEE_NUMBER types to flexible nullables
  • Added ~50 columns across:
    • Personal (BIRTH_DATE, GENDER_CODE, ETHNICITY_DESC)
    • Employment (JOB_CATEGORY_DESC, PAY_FREQUENCY_CODE, HOURLY_RATE)
    • Contact & union data (WORK_EMAIL, UNION_DSC, VETERAN_DESC)

🏛️ New Tables for Role-Based Access Control (RBAC)

Department

CREATE TABLE irby.Department (
  DepartmentId UNIQUEIDENTIFIER PRIMARY KEY,
  Name           NVARCHAR(MAX),
  Description    NVARCHAR(MAX),
  IsDeleted      BIT,
  AddedBy        NVARCHAR(MAX),
  AddedDate      DATETIME2 NOT NULL,
  ModifiedBy     NVARCHAR(MAX),
  ModifiedDate   DATETIME2 NOT NULL
);
  • Houses organizational units for employees.

Permissions

CREATE TABLE irby.Permissions (
  PermissionId          UNIQUEIDENTIFIER PRIMARY KEY,
  PermissionName        NVARCHAR(MAX),
  PermissionDescription NVARCHAR(MAX),
  IsDeleted             BIT,
  AddedBy               NVARCHAR(MAX),
  AddedDate             DATETIME2 NOT NULL,
  ModifiedBy            NVARCHAR(MAX),
  ModifiedDate          DATETIME2 NOT NULL
);
  • Defines atomic system actions (e.g., “CanEditTimesheet”).

Role

CREATE TABLE irby.Role (
  RoleId       UNIQUEIDENTIFIER PRIMARY KEY,
  Name         NVARCHAR(MAX),
  Description  NVARCHAR(MAX),
  IsDeleted    BIT,
  AddedBy      NVARCHAR(MAX),
  AddedDate    DATETIME2 NOT NULL,
  ModifiedBy   NVARCHAR(MAX),
  ModifiedDate DATETIME2 NOT NULL
);
  • Represents a collection of permissions (e.g., “Time Administrator”).

LinkRolePermission

CREATE TABLE irby.LinkRolePermission (
  Id           UNIQUEIDENTIFIER PRIMARY KEY,
  RoleId       UNIQUEIDENTIFIER NOT NULL  -- FK → Role
  PermissionId UNIQUEIDENTIFIER NOT NULL  -- FK → Permissions
  …audit fields…
);
  • M:N join linking Roles to Permissions.

LinkUserRole

CREATE TABLE irby.LinkUserRole (
  Id         UNIQUEIDENTIFIER PRIMARY KEY,
  EmployeeId UNIQUEIDENTIFIER NOT NULL    -- FK → Employee
  RoleId     UNIQUEIDENTIFIER NOT NULL    -- FK → Role
  …audit fields…
);
  • M:N join linking Employees to Roles.

ODSCustomers

CREATE TABLE ODSCustomers (
  CUSTOMER_ID     VARBINARY(MAX),
  COMPANY_ID      VARBINARY(MAX),
  ORG_OPU         NVARCHAR(MAX),
  ORG_COMPANY     NVARCHAR(MAX),
  NAME            NVARCHAR(MAX),
  DISPLAY_NAME    NVARCHAR(MAX),
  LAST_UPDATED    DATETIME2,
  CUSTOMER_NUMBER DECIMAL(18,0),
  CUSTOMER_TYPE_CODE NVARCHAR(MAX),
  CUSTOMER_TYPE_DESC NVARCHAR(MAX)
);
  • Holds raw customer data from source system.

🔑 Indexes & Foreign Keys

  • Indexes created on FK columns for performance (e.g., IX_Employee_DepartmentId, IX_LinkRolePermission_PermissionId).
  • FK Constraints enforce integrity:
    • Employee.DepartmentId → Department(DepartmentId)
    • Employee.RoleId → Role(RoleId)
    • LinkUserRole.EmployeeId → Employee(EmployeeId)
    • LinkUserRole.RoleId → Role(RoleId)
    • LinkRolePermission.PermissionId → Permissions(PermissionId)
    • LinkRolePermission.RoleId → Role(RoleId)

⏪ Rollback (Down Method)

  • Drops all newly created tables: Department, Permissions, Role, LinkRolePermission, LinkUserRole, ODSCustomers
  • Removes all added columns from Employee, ODSJobs, ODSEmployees
  • Restores original names, keys, indexes, and constraints on staging tables
protected override void Down(MigrationBuilder migrationBuilder)
{
  // 1. Drop FK & Indexes
  // 2. Drop RBAC & helper tables
  // 3. Drop added columns on Employee & staging tables
  // 4. Rename tables & columns back to staging pattern
  // 5. Re-create original PKs & FKs
}

🗺️ Entity-Relationship Diagram (ERD)

erDiagram
  IRBY.Employee ||--o{ IRBY.Department : belongs_to
  IRBY.Employee ||--o{ IRBY.Role : current_role
  IRBY.LinkUserRole }o--|| IRBY.Employee : employee
  IRBY.LinkUserRole }o--|| IRBY.Role : role
  IRBY.Role }o--o{ IRBY.LinkRolePermission : grants
  IRBY.Permissions }o--o{ IRBY.LinkRolePermission : allowed_by

🚀 Usage & Best Practices

  1. Apply Migration

    dotnet ef database update 20230713184636_AddorUpdateTablesforRBAC

  2. Seed Initial Data
    • Insert system roles (e.g., Admin, User)
    • Populate permissions (e.g., “ViewPayroll”, “EditSchedule”)
    • Link roles → permissions
  3. Assign Roles to Employees
    • Use LinkUserRole table or a service layer to manage assignments
  4. Audit & Soft-Delete
    • Leverage IsDeleted flags on RBAC tables for soft-deletes
    • Use AddedBy/ModifiedBy for user-auditing

🏁 Conclusion

This comprehensive migration transforms Irby Imaging’s data model by:

  • Formalizing RBAC support with dedicated tables
  • Enhancing Employee metadata for richer HR insights
  • Strengthening ODS staging entities for downstream analytics
  • Maintaining rollback safety via Down method

With this structure, developers can confidently build secure, auditable, and feature-rich applications on top of a robust EF Core data layer. 🎉