Current(new) structure:

Old structure:

The old ProgrammeMembership table structure was :

...

and the programmeMemberships under People menu in TIS are like (the user shown below are mocked instead of real user data):

...

So from above, we can see a person can have multiple programmeMemberships, and a programmeMembership can have multiple curriculumMemberships. But the old table has all information together, which means the table has lots of redundant programmeMembership data for curriculumMembersihp against one programmeMembership.

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

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

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