define MiVideo table relationships

[zqian]

MiVideo tables need foreign key relationships to ensure data integrity.

For example:

• mivideo_media_created.id → mivideo_media_courses.media_id

---

Update: This had been discussed by @ssciolla, @lsloan, and others in the past. It was determined that relationships between MiVideo and other tables would break when updates were made, causing errors. As Sam notes below, it makes sense to establish the relationship between mivideo_media_created and mivideo_media_courses.

[ssciolla]

@zqian, establishing foreign keys from MiVideo to the Canvas data tables cannot yet be done easily because we are dropping records in inventory and MiVideo does not. If we switch to an update (or upsert) approach for Canvas tables, we can probably make this happen. In this respect, this issue is a duplicate of #162.

However, you are right that we are missing a foreign key between mivideo_media_created and mivideo_media_courses. This can and should be added.

[lsloan]

@zqian, I've updated the issue description above. Of the two examples you gave, @ssciolla and I agree that relationships between MiVideo tables can be maintained. However, other relationships are problematic. If you have any other relationships to suggest, please add them to the list in the description.

[zqian]

Thanks for the suggestions, @ssciolla and @lsloan. I removed the original FK proposal

[lsloan]

I removed the "bug" label because the definition of that label is "Something isn't working". In this case, it is working. This would be an enhancement, so I've added that label.

[zqian]

verified the added FK in local MySQL instance.