Skip to content

Latest commit

 

History

History
289 lines (206 loc) · 12.2 KB

2-Employee-DB.md

File metadata and controls

289 lines (206 loc) · 12.2 KB

Step 2 - Employee DB

This will walk through the process of creating the required tables, and entity framework capabilities, etc. needed for the Employee within a Microsoft SQL Server database. All of this work will occur within the context of the MyEf.Hr.Database project.

The Beef.Database.SqlServer and DbEx provide the capabilities that will be leveraged. The underlying documentation describes these capabilities and the database approach in greater detail.


Terminal

The database codegen and DbEx commands will run from a command-line interface. In preparation for the steps below, open a new terminal/command-prompt and navigate to the MyEf.Hr.Database base folder directory

Note: To see all supported command line options execute dotnet run -- --help.


Entity relationship diagram

The following provides a visual (ERD) for the database tables that will be created. A relationship label of refers indicates a reference data relationship. The has-JSON indicates that the relating entity references via a JSON data column (not a referenced database table).

erDiagram
    Employee ||--o{ EmergencyContact : has
    Employee }|..|| Gender : refers
    Employee }|..o| TerminationReason : refers
    Address }|..|| USState : refers
    Employee ||--o{ Address : has-json
    EmergencyContact }|..|| RelationshipType : refers
Loading

Clean up existing migrations

Within the Migrations folder there will already be three entries that were created during the initial solution skeleton creation. These should all be removed.

└── Migrations
  └── 20190101-000001-create-Hr-schema.sql     <- remove
  └── 20190101-000002-create-Hr-Gender.sql     <- remove
  └── 20190101-000003-create-Hr-Person.sql     <- remove

Create HR schema

Create the Hr schema using the database tooling. The following command will create the migration script using the pre-defined naming convention and templated T-SQL to aid development.

dotnet run script schema Hr

Create Employee table

Create the migration script for the Employee table within the Hr schema, following a similar naming convention, to ensure it is executed (applied) in the correct order. The following command will create the migration script using the pre-defined naming convention and templated T-SQL to aid development.

dotnet run script create Hr Employee

For the purposes of this step, open the newly created migration script and replace its contents with the following. Additional notes have been added to give context/purpose where applicable. Note that the reference data values use Code and that no coresponding database constraint is added; this relationship (and consistency) is managed by the owning business logic.

-- Create table: [Hr].[Employee]

BEGIN TRANSACTION

CREATE TABLE [Hr].[Employee] (
  [EmployeeId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWSEQUENTIALID()) PRIMARY KEY,  -- This is the primary key
  [Email] NVARCHAR(250) NULL UNIQUE,                                               -- This is the employee's unique email address
  [FirstName] NVARCHAR(100) NULL,
  [LastName] NVARCHAR(100) NULL,
  [GenderCode] NVARCHAR(50) NULL,                                                  -- This is the related Gender code; see Hr.Gender table
  [Birthday] DATE NULL,    
  [StartDate] DATE NULL,
  [TerminationDate] DATE NULL,
  [TerminationReasonCode] NVARCHAR(50) NULL,                                       -- This is the related Termination Reason code; see Hr.TerminationReason table
  [PhoneNo] NVARCHAR(50) NULL,
  [AddressJson] NVARCHAR(500) NULL,                                                -- This is the full address persisted as JSON.
  [RowVersion] TIMESTAMP NOT NULL,                                                 -- This is used for concurrency version checking. 
  [CreatedBy] NVARCHAR(250) NULL,                                                  -- The following are standard audit columns.
  [CreatedDate] DATETIME2 NULL,
  [UpdatedBy] NVARCHAR(250) NULL,
  [UpdatedDate] DATETIME2 NULL
);
	
COMMIT TRANSACTION

Create Emergency Contacts table

Use the following command line to generate the migration script to create the EmergencyContact table within the Hr schema.

dotnet run script create Hr EmergencyContact

Replace the contents with the following. Note: the row version and auditing columns have been removed as these are not required as this table is tightly-coupled to Employee, and therefore can only (and should only) be updated in that context (i.e. is a sub-table).

-- Create table: [Hr].[EmergencyContact]

BEGIN TRANSACTION

CREATE TABLE [Hr].[EmergencyContact] (
  [EmergencyContactId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWSEQUENTIALID()) PRIMARY KEY,
  [EmployeeId] UNIQUEIDENTIFIER NOT NULL,
  [FirstName] NVARCHAR(100) NULL,
  [LastName] NVARCHAR(100) NULL,
  [PhoneNo] NVARCHAR(50) NULL,
  [RelationshipTypeCode] NVARCHAR(50) NULL
);
	
COMMIT TRANSACTION

Create Reference Data tables

To support the capabilities of the tables above the following Reference Data tables are also required.

  • Hr.Gender
  • Hr.TerminationReason
  • Hr.RelationshipType
  • Hr.USState

At the command line execute the following commands. This will automatically create the tables as required using the reference data template given the refdata option specified. No further changes will be needed for these tables.

dotnet run script refdata Hr Gender
dotnet run script refdata Hr TerminationReason
dotnet run script refdata Hr RelationshipType
dotnet run script refdata Hr USState

Reference Data data

Now that the Reference Data tables exist they will need to be populated. It is recommended that where possible that the Production environment values are specified (as these are intended to be deployed to all environments).

These values (database rows) are specified using YAML. For brevity in this document we will grab the data we need from RefData.yaml. Take the contents of this file, copy and replace the contents of the prefilled RefData.yaml within the My.Hr.Database/Data folder. Finally, remove the PerformanceOutcome lines at the end of the file (we are not creating or populating this table for now).

Note: The format and hierarchy for the YAML, is: Schema, Table, Row. For reference data tables where only Code: Text is provided, this is treated as a special case shorthand to update those two columns accordingly (the other columns will be updated automatically). The $ prefix for a table indicates a merge versus an insert (default).

Hr:
  - $Gender:
    - F: Female
    - M: Male
    - N: Not specified
  - $TerminationReason:
    - RE: Resigned
    ...
  ...

Reference Data query

To support the requirement to query the Reference Data values from the database we will use Entity Framework (EF) to simplify. The Reference Data table configuration will drive the EF .NET (C#) model code-generation via the efModel: true attribute.

Remove all existing configuration from database.beef-5.yaml and replace with the contents below. Each table configuration is referencing the underlying table and schema, then requesting an EF model is created for all related columns found within the database. Beef will query the database to infer the columns during code-generation to ensure it "understands" the latest configuration.

# Configuring the code-generation global settings
# - Schema defines the default for all tables unless explicitly defined.
# - EfModel indicates that an Entity Framework model should be generated for all tables unless specified otherwise.
# 
schema: Hr
efModel: true
tables:
  # Reference data tables/models.
- name: Gender
- name: TerminationReason
- name: RelationshipType
- name: USState

Entity Framework CRUD and query

Entity Framework will be used for the primary Employee CRUD as this also allows a simplified (and performant) means to select and update related tables as required, in this case EmergencyContact.

Copy the following configuration and append (after reference data) to the database.beef-5.yaml; see comments within for the details. Again, Beef will query the database to infer the columns during code-generation.

  # References the Employee and related tables to implement the EF Model and infer the underlying schema.
- name: Employee
  relationships: [
    # Relationships can be code-generated (basic functionality), or handcrafted in the .NET code using the standard EntityFramework capabilities.
    # - One-to-many to EmergencyContacts table foreign key using EmployeeId column. Cascade the delete. Auto include collection on get and track for updates.
    { name: EmergencyContact, propertyName: EmergencyContacts, foreignKeyColumns: [ EmployeeId ], onDelete: ClientCascade, autoInclude: true }
  ]

- name: EmergencyContact

Database management

Once the configuration has been completed then the database can be created/updated, the code-generation performed, and the corresponding reference data loaded into the corresponding tables.

At the command line execute the following command to perform. The log output will describe all actions that were performed.

dotnet run all

If at any stage the database becomes corrupted or you need to rebuild, execute the following to drop and start again.

dotnet run drop

Indexes, etc.

Where tables need indexes and other constraints added these would be created using additional migration scripts. None have been included in the sample for brevity.


Event outbox

To support the transactional outbox pattern there is the need to have backing event queue tables. The migration scripts (and .NET code) to support this requirement can be code generated by adding the following to the top of the database.beef-5.yaml file directly under the schema: hr line.

outbox: true

Execute the code-generation again using codegen option to generate the newly configured migration scripts. Once generated the database can be updated to use by using the database option. Run both of the following commands.

dotnet run codegen
dotnet run database

Verify

At this stage we now have a working database ready for the consuming API logic to be added. The required database tables exist, the Reference Data data has been loaded, the required stored procedures and user-defined type (UDT) for the Event outbox have been generated and added to the database. The .NET (C#) Entity Framework models have been generated and added to the My.Hr.Business project, including the requisite event outbox enqueue/dequeue capabilities.

To verify, confirm you have the below set of sql migration scripts:

└── Migrations
  └── <date>-<number>-create-hr-schema.sql
  └── <date>-<number>-create-hr-employee-table.sql
  └── <date>-<number>-create-hr-emergencycontact-table.sql
  └── <date>-<number>-create-hr-gender-refdata-table.sql
  └── <date>-<number>-create-hr-terminationreason-refdata-table.sql
  └── <date>-<number>-create-hr-relationshiptype-refdata-table.sql
  └── <date>-<number>-create-hr-usstate-refdata-table.sql
  └── <date>-<number>-01-create-outbox-schema.sql
  └── <date>-<number>-02-create-outbox-eventoutbox-table.sql
  └── <date>-<number>-03-create-outbox-eventoutboxdata-table.sql

Confirm also, the following DB and tables have been created (utilizing a DB tool such as Azure Data Explorer or SSMS):

└── Local
  └── Databases
    └── MyEf.Hr
      └── Tables
        └── Hr.EmergencyContact
        └── Hr.Employee
        └── Hr.Gender
        └── Hr.RelationshipType
        └── Hr.TerminationReason
        └── Hr.USState
        └── Outbox.EventOutbox
        └── Outbox.EventOutboxData

Next Step

Next, we need to create the employee API endpoint to perform the desired CRUD operations.