Business rules

Entity relationships:

1. A programme membership may be linked to zero [one?] or more placements.

2. A placement will be linked to at most [exactly?] one programme membership.

3. Given 1 and 2 above, a programme membership may have no related placements, and a placement may exist independently of any programme membership. This would allow legacy (unlinkable) data to be retained without particular exception-handling, and existing data bulk upload / editing functionality to be unaffected. In future this could be tightened-up if appropriate.

4. [There may be circumstances where a placement is linked to more than one programme membership. Specific examples TBC.]

5. Some historic placements / programme memberships may need to remain unlinked (dirty data, ambiguity).

6. Which placements are eligible to be linked to a programme membership are those for the same trainee where the placement post is the same as the programme post, i.e.:

Ambiguity in this linkage can be seen here: https://build.tis.nhs.uk/metabase/question/notebook#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiOiJuYXRpdmUiLCJuYXRpdmUiOnsicXVlcnkiOiJzZWxlY3QgcGwuaWQsIGNvdW50KCopIFxyXG5GUk9NIHRjcy5Qcm9ncmFtbWVNZW1iZXJzaGlwIHBtIFxyXG5KT0lOIHRjcy5Qcm9ncmFtbWUgcHJnIE9OIHBtLnByb2dyYW1tZUlkID0gcHJnLmlkXHJcbkpPSU4gdGNzLlByb2dyYW1tZVBvc3QgcHAgT04gcHJnLmlkID0gcHAucHJvZ3JhbW1lSWRcclxuSk9JTiB0Y3MuUGxhY2VtZW50IHBsIE9OIHBsLnBvc3RJZCA9IHBwLnBvc3RJZCBBTkQgcG0ucGVyc29uSWQgPSBwbC50cmFpbmVlSWRcclxuV0hFUkUgcG0ucHJvZ3JhbW1lU3RhcnREYXRlID4gJzIwMjQtMDEtMDEnXHJcbkdST1VQIEJZIHBsLmlkXHJcbkhBVklORyBjb3VudCgqKSA-IDE7IiwidGVtcGxhdGUtdGFncyI6e319LCJkYXRhYmFzZSI6MzZ9LCJkaXNwbGF5IjoidGFibGUiLCJ2aXN1YWxpemF0aW9uX3NldHRpbmdzIjp7fX0=
select pl.id, count()
FROM tcs.ProgrammeMembership pm
JOIN tcs.Programme prg ON pm.programmeId = prg.id
JOIN tcs.ProgrammePost pp ON prg.id = pp.programmeId
JOIN tcs.Placement pl ON pl.postId = pp.postId AND pm.personId = pl.traineeId
WHERE pm.programmeStartDate > '2024-01-01'
GROUP BY pl.id
HAVING count() > 1;

6. Enhanced placement eligibility criteria (TBC): specialties must also match, i.e.:

Ambiguity in this linkage can be seen here:
https://build.tis.nhs.uk/metabase/question/notebook#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiOiJuYXRpdmUiLCJuYXRpdmUiOnsicXVlcnkiOiJzZWxlY3QgaWQsIGNvdW50KCopXHJcbkZST00gKFxyXG5TRUxFQ1QgcGwuaWQsIHBtLnV1aWQgRlJPTVxyXG50Y3MuUHJvZ3JhbW1lTWVtYmVyc2hpcCBwbSBcclxuSk9JTiB0Y3MuQ3VycmljdWx1bU1lbWJlcnNoaXAgY20gT04gcG0udXVpZCA9IGNtLnByb2dyYW1tZU1lbWJlcnNoaXBVdWlkXHJcbkpPSU4gdGNzLkN1cnJpY3VsdW0gYyBPTiBjbS5jdXJyaWN1bHVtSWQgPSBjLmlkXHJcbkpPSU4gdGNzLlByb2dyYW1tZSBwcmcgT04gcG0ucHJvZ3JhbW1lSWQgPSBwcmcuaWRcclxuSk9JTiB0Y3MuUHJvZ3JhbW1lUG9zdCBwcCBPTiBwcmcuaWQgPSBwcC5wcm9ncmFtbWVJZFxyXG5KT0lOIHRjcy5Qb3N0U3BlY2lhbHR5IHBzIE9OIHBwLnBvc3RJZCA9IHBzLnBvc3RJZCBBTkQgYy5zcGVjaWFsdHlJZCA9IHBzLnNwZWNpYWx0eUlkXHJcbkpPSU4gdGNzLlBsYWNlbWVudCBwbCBPTiBwbC5wb3N0SWQgPSBwcC5wb3N0SWQgQU5EIHBtLnBlcnNvbklkID0gcGwudHJhaW5lZUlkXHJcbldIRVJFIHBtLnByb2dyYW1tZVN0YXJ0RGF0ZSA-ICcyMDI0LTAxLTAxJ1xyXG5HUk9VUCBCWSBwbC5pZCwgcG0udXVpZFxyXG4pIHhcclxuR1JPVVAgQlkgaWRcclxuSEFWSU5HIGNvdW50KCopID4gMSIsInRlbXBsYXRlLXRhZ3MiOnt9fSwiZGF0YWJhc2UiOjM2fSwiZGlzcGxheSI6InRhYmxlIiwidmlzdWFsaXphdGlvbl9zZXR0aW5ncyI6e319
select id, count()
FROM (
SELECT pl.id, pm.uuid FROM
tcs.ProgrammeMembership pm
JOIN tcs.CurriculumMembership cm ON pm.uuid = cm.programmeMembershipUuid
JOIN tcs.Curriculum c ON cm.curriculumId = c.id
JOIN tcs.Programme prg ON pm.programmeId = prg.id
JOIN tcs.ProgrammePost pp ON prg.id = pp.programmeId
JOIN tcs.PostSpecialty ps ON pp.postId = ps.postId AND c.specialtyId = ps.specialtyId
JOIN tcs.Placement pl ON pl.postId = pp.postId AND pm.personId = pl.traineeId
WHERE pm.programmeStartDate > '2024-01-01'
GROUP BY pl.id, pm.uuid
) x
GROUP BY id
HAVING count() > 1

TBC: do postSpecialtyType values impact this?

7. Even with these constraints, ambiguities arise where a placement could be linked to more than one programme membership. Start and end dates may be useful in resolving these, but these may be less reliable.

8. No additional details are needed for a linkage [TBC]. It may be useful to record metadata such as (a) when the linkage was made and (b) who made it, or whether it was automatically made.

Data ownership:

1. The linking of placements to programme memberships will be managed by LOs, not trainees.

2. In TIS Admin, the users who manage placement and programme membership data will be able to add and remove the linkages between the records they manage. There is no obvious reason to keep a record of linkages after they are removed.

Data model: TIS

1. A new TCS table ProgrammeMembershipPlacement containing FK fields programmeMembershipUuid, placementId, and any metadata fields (timestamp when added, by whom). Deletions of placements or programme memberships should cascade delete related records in this table. The PK for this table could be a composite (programmeMembershipUuid + placementId) or a synthetic id field, but (programmeMembershipUuid + placementId) should in any event be unique.

Data model: TSS

TODO.