...
- accountId [FK] - optional
- studyId [FK] - required
- subStudyId [FK] - required
- externalId - optional. developers/admins wouldn't have one
Note: in Hibernate, externalId may need to be part of the key, and if so, cannot be null. It could be auto-generated and we might want to keep track of that.
Migrate all the existing external IDs from DynamoDb to SQL tables and adjust all the existing APIs to use the new external Ids table. This means adjusting some APIs to look up users through this association rather than the DDB table.
...