...
- 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 thatfor this to be an entity, it'll need a primary composite key based on the foreign keys in the table. Not sure how, in this case, we can leave accountId empty.
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.
...