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 the refactoring is 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:

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

Rename ProgrammeMembership table to CurriculumMembership

TIS21-2420 - Getting issue details... STATUS

1. Duplicate ProgrammeMembership table to a new table CurriculumMembership as a exact copy

2. Start writing updates to both tables

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

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

Rebuild new ProgrammeMemberhsip and populate the actual data

1. Drop old ProgrammeMembership table, and create a empty new table with necessary fields only.

2. Insert data via grouped curriculumMembership.

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

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

Queries

Downstream services:

NDW-ETL

The queries in NDW-ETL

TIS21-2397 - Getting issue details... STATUS

TIS-Sync

TIS21-2392 - Getting issue details... STATUS

Assessment

TIS21-2422 - Getting issue details... STATUS

Bulk Upload

Reval

TSS

ESR

Lambdas on AWS