[discussion] Extract MRI fields from `session` into a new table

[maximemulder]

TO BE DISCUSSED AT THE NEXT IMAGING MEETING

This is a proposal I have for a database structure change. It reflects my understanding of the database but feel free to correct me if I am making mistakes.

Current architecture

Currently, the session table contains several fields related to MRI such as Scan_done, MRIQCStatus, MRIQCPending, MRIQCFirstChange, MRIQCLastChange, and MRICaveat (and maybe others I am forgetting). Some of these fields are nullable, and many imaging tables have foreign keys to the session table.

Proposal

There was a proposal to remove Scan_done a few weeks ago and instead check if the exists an MRI file linked to the session. I propose taking this proposal one step further and extract all MRI-related fields from session into a new table (probably mri_session). The imaging tables would also refer to mri_session instead of session directly. If possible, we could also extract some fields common between all MRI files of a single session to this table (notably scanner ID ?).

mri_session would have a foreign key linking it to its related session record, which provides information, which notably links it back to its candidate and visit. This foreign key should be nullable so that we can have MRI sessions not related to any candidate / visit session, which we want to clean up our phantoms implementation.