Business rules

Entity relationships:

1. A programme membership (PM) may be linked to one or more placements [legacy: zero or more].

2. A placement will be linked to exactly one PM [legacy: could be unlinked to any PM].

3. Given 1 and 2 above, for legacy records, a PM may have no related placements, and a placement may exist independently of any PM. 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 currently linked to more than one PM, but this should be corrected.

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

6. Over the duration of a PM, there is expected to be one or more placements active at all times.

7. A placement should not extend before the beginning of its linked PM, but some dirty data may be expected (e.g. induction periods / pre-start before the PM-proper begins).

8. A placement may extend after the end of its linked PM, particularly in the case of e.g. LTFT when the placement has been extended but the PM record has not been adjusted to match.

9. Which placements are eligible to be linked to a PM are not necessarily those for the same trainee where the placement post is the same as the programme post (see Note 1 below).

10. Likewise, specialty is not a useful way of filtering possible linkages (see Note 2 below).

11. A placement with grade abbreviation of F1 or F2 should be linked to a PM with curriculum specialty ‘Foundation'.

12. Start and end dates should be used to limit options for linkage [may need some fuzziness on start-date, and more on end-date].

    1. If placement start-date is within the PM, then it should be linked to that PM.

    2. If placement start date > 13 days before start of PM, and placement end-date is within PM, then link to this PM.

    3. This is a slight variation on the rules used to identify placements in the notifications pilot by linking them to PMs in the pilot, see note below.

    4. If a placement falls within multiple PMs, but ends on the end date of a particular one, that is the preferred linkage.

13. Can also consider how many PMs they had at the point where the placement was created (this may require some fuzziness on dates, again). If there was only one, then link to that.

14. No additional details need to be recorded against a linkage. However, it may be useful to record metadata such as

    1. when the linkage was made and

    2. who made it, or whether it was automatically made and

    3. if auto-linked, the results of the conditions that were evaluated, and the assessed probability.

15. When evaluating auto-linkages, it will be useful to segment the data as follows, and potentially to implement differentially across the 4 quadrants:

    1. Placements / PMs with a start date prior to the roll-out of TIS, i.e. 2018 (check exact date) are LEGACY, the remainder are RECENT.

    2. Placements / PMs related to active trainees (those with ongoing or upcoming PMs / placements) are ACTIVE and those related to non-active trainees are INACTIVE.

    3. Priority for RECENT-ACTIVE placements will be highest, and that of LEGACY-INACTIVE the lowest.

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 PM 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 PlacementProgrammeMembership containing FK fields programmeMembershipUuid, placementId, and any metadata fields (timestamp when added, by whom, matching conditions, probability).

2. Deletions of placements or PMs should cascade delete related records in this table.

3. 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

1. TraineeProfile: add linkedProgrammeMembership[]: {uuid, when, how, metrics} to placement subdocument. The actual details of the PM are already held in the programmeMembership[] subdocuments.

2. tis-trainee-sync: add placementProgrammeMembership collection to be synced from TIS, and triggering re-enrichment of related placements. For the first phase, the only sync will be the Placement-ProgrammeMembership-Link table from TIS. When an INSERT/UPDATE/DELETE record arrives in tis-trainee-sync for that table we would need to update the sync collection, re-enrich the related placement and update the trainee profile accordingly.
   In addition, send this new trainee profile content to the NDW.
   In phase 1, the new linkedProgrammeMembership data should not affect TSS credentials, forms, notifications or actions,

Notes:

1. Post linkage (not reliable):
   Sometimes placements ‘borrow’ post from another programme because otherwise post management becomes laborious, e.g. Emergency Medicine Higher → EM run-through; GP using Obstetrics etc. in hospital placement.

Open

Even if this were a solid way of filtering programmes, there is some ambiguity in this linkage, as 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;

2. Specialty linkage (not reliable):
   This doesn’t work because e.g. GPs have to 12 months of 'hospital' placements which will not have the specialty of General Practice.

Open

Even if this were a solid way of filtering programmes, there is still some ambiguity in this linkage, as 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

3. When the notifications pilot was run, we had a slightly different rule to identify placements that belonged to pilot programme memberships. This rule had a similar rate of ambiguous / missing matches (and in the context we didn't mind, since it was intended purely to identify if there was at least one programme membership that the placement might belong to that was in the pilot, rather than identify that PM unambiguously).

   New rule:
   - placement start date between PM start and end dates OR
   - placement start date no more than 13 days before the PM start date AND placement end date no later than PM end date

   The rule we used for the pilot logic:
   - placement start date not before 1st of month of PM start date AND placement start date no later than PM end date
   Comparison:
   SQL new rule (example for no matches, 2023):
   SELECT
count(*)
-- pl.id, pl.dateFrom, pl.dateTo, matches, minpmuuid, pmmin.programmeStartDate, pmmin.programmeEndDate, maxpmuuid, pmmax.programmeStartDate, pmmax.programmeEndDate
FROM tcs.Placement pl JOIN
(
SELECT id, sum(ismatch) as matches, min(uuid) as minpmuuid, max(uuid) as maxpmuuid FROM
(
SELECT pl.id, pm.uuid, if((pl.dateFrom >= pm.programmeStartDate AND pl.dateFrom <= pm.programmeEndDate)
OR (date_add(pl.dateFrom, interval 13 day) >= pm.programmeStartDate AND pl.dateTo <= pm.programmeEndDate), 1, 0) AS isMatch FROM
tcs.Placement pl
JOIN tcs.ProgrammeMembership pm ON pm.personId = pl.traineeId
WHERE (pl.dateFrom >= "2022-01-01" AND pl.dateFrom < "2023-01-01")
) plpm_match
GROUP BY id
) plsummary ON pl.id = plsummary.id
JOIN tcs.ProgrammeMembership pmmin ON pmmin.uuid = plsummary.minpmuuid
JOIN tcs.ProgrammeMembership pmmax ON pmmax.uuid = plsummary.maxpmuuid
WHERE matches = 0
   Pilot rule (example for no matches, 2023):
   SELECT
count(*)
-- pl.id, pl.dateFrom, pl.dateTo, matches, minpmuuid, pmmin.programmeStartDate, pmmin.programmeEndDate, maxpmuuid, pmmax.programmeStartDate, pmmax.programmeEndDate
FROM tcs.Placement pl JOIN
(
SELECT id, sum(ismatch) as matches, min(uuid) as minpmuuid, max(uuid) as maxpmuuid FROM
(
SELECT pl.id, pm.uuid, if((pl.dateFrom >= CAST(DATE_FORMAT(pm.programmeStartDate ,'%Y-%m-01') as DATE) AND pl.dateFrom <= pm.programmeEndDate), 1, 0) AS isMatch FROM
tcs.Placement pl
JOIN tcs.ProgrammeMembership pm ON pm.personId = pl.traineeId
WHERE (pl.dateFrom >= "2022-01-01" AND pl.dateFrom < "2023-01-01")
) plpm_match
GROUP BY id
) plsummary ON pl.id = plsummary.id
JOIN tcs.ProgrammeMembership pmmin ON pmmin.uuid = plsummary.minpmuuid
JOIN tcs.ProgrammeMembership pmmax ON pmmax.uuid = plsummary.maxpmuuid
WHERE matches = 0
   
   

Note that the table above excludes placements for the 2-3% of trainees who have no programme memberships set up. Revised estimates for recent years including different linking methods are summarised below: