Current(new) structure:

Old structure:

The old ProgrammeMembership table structure was :

...

Under the old table structure, when we need to extract a real programmeMembership, we use personId, programmeStartDate, programmeEndDate, programmeId, programmeMembershipType fields for identification. When there’s a field above with missing data(data issue), some problems occurs for querying all curriculum memberships against a specific programmeMembership.

Why is the refactoring

...

necessary?

please see: https://hee-tis.atlassian.net/wiki/spaces/NTCS/pages/106659893/Programmes+-+Programme+memberships#Why-the-distinction-between-the-2-is-necessary%3F

Distinction between 2 entities

Current(new) table structure:

...

...

Steps of refactoring:

To avoid thinking of naming of some interim tables, we decided to:

1. Rename ProgrammeMembership table to CurriculumMembership

• Duplicate ProgrammeMembership table to a new table CurriculumMembership as a exact copy

• Start writing updates to both tables

• Modify all the queries (downstream services included) to use new CurriculumMembership.

• Monitor for a few weeks to make sure there’re no queries to old ProgrammeMembership.

2. Rebuild new ProgrammeMemberhsip and populate the actual data

• Drop old ProgrammeMembership table, and create

...

• an empty new table with necessary fields only.

• Insert data via grouped curriculumMembership.

• Update CurriculumMembership table with adding a new foreign key field programmeMembershipUuid and populate data for the foreign key.

• Save / Update in both new ProgrammeMembership and CurriculumMembership tables in parallel.

After the Step 2, the Interim table structure is as below:

...

3. Modify all the reference to deprecated fields to use ProgrammeMembership table only

TCS sql, queries:

Downstream services:

NDW-ETL

The queries Queries in NDW-ETL related to programme/curriculum memberships

TIS-Sync

Assessment

Bulk Upload

Bulk upload sends requests to TCS via tcs-client. As long as tcs queries are refactored, bulk upload is okay.

Reval

The data sync from TIS to Reval uses traineeInfoForConnection.sql in tcs-persistence.

TSS

ESR: not affected

Lambdas on AWS: not affected

4. Remove parallel saving and remove deprecated fields