Content Comparison

...

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

2. A placement will be linked to at most [ exactly ?] one programme membership [legacy: could be unlinked to any PM].

3. Given 1 and 2 above, for legacy records, 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 currently linked to more than one programme membership. Specific examples TBC., but should be corrected.]

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

6. Over the duration of a programme membership, is there expected to be one or more placements active at all times [yes]? Can a placement extend beyond the beginning [no, but could be dirty data, or e.g. induction/pre-start]/end [yes] of its linked programme membership?

7. 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 [no, sometimes placement ‘borrows’ post from another programme because otherwise post management becomes laborious, e.g. emergency medicine higher → EM run-through; GP using obstetrics etc. in hospital], 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 [no, e.g. GP], 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? [ignore]

7. Even with these constraints, ambiguities arise where a placement could be linked to more than one programme membership.

8. Start and end dates should be used to limit options for linkage [may be useful in resolving these, but these may be less reliableneed some fuzziness on start-date, and more on end-date]. If Pl start-date is within PM, then it should be linked to that PM. If Pl start date > 13 days before start of PM + Pl end-date is within PM, then link to this PM.

9. Can also consider how many PMs they had at the point where the Pl was created (may needs some fuzziness on dates, again).

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

11. Legacy vs current: Pl / PM with start date prior to roll-out of TIS, i.e. 2018 (check exact date). Current trainee might also have legacy data (segment current vs legacy, and legacy vs current for current trainees). [Could include confidence of linkage - e.g. matching conditions met.]

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.

...