DoktorNik / course-companion

BrockU CSC Course Companion
1 stars 1 forks source link

Migrate eligible concentration course relationship to single-pivot representation #23

Closed gideonoludeyi closed 3 months ago

gideonoludeyi commented 3 months ago

The current pattern for forming many-to-many relationships between students and courses uses two pivot tables. For example, the eligible concentration course relationship, which associates students with the courses required for their concentration, is represented in the following manner.

Untitled diagram (1)
Double-pivot Relationship

It's possible to represent this relationship using a single pivot table instead.

Untitled
Single-pivot relationship

This PR introduces a database migration to represent the eligible concentration course relationship using a single-pivot relationship approach and copies over any existing data from the double-pivot relationship.

The double-pivot relationship is kept for now to make it easy to rollback in case of any issues with the single-pivot implementation. Data is being written to both relationships at the same time, but data is being read only from the single-pivot relationship.

Once the single-pivot relationship has been proven to work correctly in production, a subsequent PR should be created to remove the double-pivot relationship.

[!NOTE] This PR is experimental to test and to identify any issues with this migration approach. Once this migration approach is verified in production, the other double-pivot relationships will be migrated to single-pivot relationships in a future PR.

DoktorNik commented 3 months ago

Great work, @gideonoludeyi Thank you!