...
// Either or both of accountId and externalId need to be filled out. Would probably use ID column to avoid composite keys, but then would need to verify the results conform to the caller's study and sub-study.
CREATE TABLE `ExternalIds` {
`id` VARCHAR(60) NOT NULL,
`studyId` VARCHAR(60) NOT NULL,
`subStudyId` VARCHAR(60) NOT NULL,
`accountId` VARCHAR(255) NULL,
`externalId` VARCHAR(255) NULL,
PRIMARY KEY (`id`)
UNIQUE INDEX `StudyId-SubStudyId-Index` (`studyId` ASC, `subStudyId` ASC),
UNIQUE INDEX `StudyId-ExternalId-Index` (`studyId` ASC, `externalId` ASC),
UNIQUE INDEX `StudyId-AccountId-Index` (`studyId` ASC, `accountId` ASC)
}
...
Note: in Hibernate, for this to be an entity, it'll need a primary composite key based on the foreign keys in the table. Or actually I like @IdClass for this purpose.Not sure how, in this case, we can leave accountId empty.
Further tasks in no particular order:
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.
...