📑 Migration Documentation: 20230713184636_AddorUpdateTablesforRBAC.cs
Table of Contents
- Overview & Purpose
- Migration Workflow
- Schema Renames & Column Adjustments
- Extending the
EmployeeEntity - Enriching ODS Staging Tables
- New Tables for Role-Based Access Control (RBAC)
- Indexes & Foreign Keys
- Rollback (
DownMethod) - Entity-Relationship Diagram (ERD)
- Usage & Best Practices
- 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
- Drop obsolete foreign keys, primary keys & unique constraints
- Rename tables and columns for naming consistency
- Alter existing columns (e.g., make IDs non-nullable)
- Add dozens of new metadata columns to:
EmployeeODSJobs(formerlyODS_Jobs_Staging)ODSEmployees(formerlyODS_Employee_Staging)
- Create new RBAC & helper tables:
Department,Permissions,Role,LinkRolePermission,LinkUserRole,ODSCustomers
- Define indexes & foreign keys to enforce relationships
- Provide a
Downmethod 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&TimeEntryTypeIdbecome non-nullable (Guid) with defaultGuid.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&RoleIdare optional, support organizing employees into teams & system roles.
🔄 Enriching ODS Staging Tables
ODSJobs (formerly ODS_Jobs_Staging)
- Changed
ORG_BUtype from fixed-length key to free-formnvarchar(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_NUMBERtypes 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)
- Personal (
🏛️ 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
- Apply Migration
dotnet ef database update 20230713184636_AddorUpdateTablesforRBAC - Seed Initial Data
- Insert system roles (e.g., Admin, User)
- Populate permissions (e.g., “ViewPayroll”, “EditSchedule”)
- Link roles → permissions
- Assign Roles to Employees
- Use
LinkUserRoletable or a service layer to manage assignments
- Use
- Audit & Soft-Delete
- Leverage
IsDeletedflags on RBAC tables for soft-deletes - Use
AddedBy/ModifiedByfor user-auditing
- Leverage
🏁 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
Downmethod
With this structure, developers can confidently build secure, auditable, and feature-rich applications on top of a robust EF Core data layer. 🎉