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 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 of refactoring:

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

1. Rename ProgrammeMembership table to CurriculumMembership

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.

2. Rebuild new ProgrammeMemberhsip and populate the actual data

1. Drop old ProgrammeMembership table, and create

...

1. an 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.

Interim table structure:

Queries

Downstream services:

NDW-ETL

The queries in NDW-ETL

TIS-Sync

Assessment

Bulk Upload

Reval

TSS

ESR

Lambdas on AWS