Summary
The primary keys are the unique identifiers for each table. They make sure each record can be distinguished from every other record. For example, laboratory_id uniquely identifies each laboratory, contact_id each study contact, study_id each clinical study, user_id each application user, role_id each role, participant_id each study participant, visit_id each study visit, and file_id each clinical file. In the app_user_role table, the primary key is a composite key made from (user_id, role_id), which means the same user-role combination cannot be entered twice. The tables are linked through foreign key relationships. A clinical_study belongs to one laboratory through laboratory_id, and it can also point to one main study_contact through primary_contact_id. Users and roles are connected through the app_user_role bridge table, which allows one user to have multiple roles and one role to belong to multiple users. The study_participant table connects participants to a study, and its participant_id is also linked to the external participant_information table, which represents where the real identifying personal data would live. Then study_visit links to study_participant, meaning each visit belongs to one participant in the study. Finally, clinical_file links back to the study, participant, and optionally a visit, so each stored file can be tied to the right context.
