...
- studyId [PK=studyId/ID]
- ID (either an int or a string)
- name/label
- externalIdAssigned - if true, must successfully find and assign an external ID if one is provided on creation or update. If false, simply create an associative record without using external ID. Admin accounts always operate as if this were false (they don't need an external ID). This is open to adjustment. For example, we might create a random external ID if desired.
Create v4 ExternalIds table (SQL tables, server APIs, SDK and integration tests)
This is an associative table (optionally) with the additional metadata of an external ID that defines there relationship for lookup. It is possible for this to exist without an associated user, as when we import external IDs to validate them when accounts are created. It's also possible to have the association without an external ID (Table might be better called SubStudyAccounts).
- accountId [FK] - optional
- studyId [FK] - required
- subStudyId [FK] - required
- externalId - optional. developers/admins wouldn't have one
...