Definition

The User In Role data model represents users and their roles within an organisation. This model includes detailed information about each user’s role, such as job category, contract dates, and professional identifiers. It is designed to provide a comprehensive view of the roles and responsibilities of users within an organisation, facilitating better management and analysis of user roles and their associated data.

Information

The data model captures the roles of users across various organizations. Each user may have multiple roles, with each role represented by a unique combination of user and role-specific data.

Overview

flowchart TD
    n01["Organisation 1001"]@{ shape: rect}
    n02["Organisation 1002"]@{ shape: rect}
    n1["User 123"]@{ shape: rect}
    n11["UserInRole 1231"]@{ shape: rect}
    n2["User 456"]@{ shape: rect}
    n21["UserInRole 4561"]@{ shape: rect}
    n22["UserInRole 4562"]@{ shape: rect}
    n3["User 789"]@{ shape: rect}
    n31["UserInRole 7891"]@{ shape: rect}
    n91["UserInRoles"]@{ shape: extract}
    n92["ETL"]@{ shape: event}
    n93["User In Role Model"]@{ shape: internal-storage}

    n1 --> n11 --> n91
    n2 --> n21 --> n91
    n2 --> n22 --> n91
    n3 --> n31 --> n91
    n01 --> n1
    n02 --> n2
    n02 --> n3
    n91 --> n92
    n92 --> n93

    classDef nodeStyle stroke:#9961a4;
    class n1,n2,n3,n01,n02,n11,n21,n22,n31,n91,n92,n93 nodeStyle;

    linkStyle default stroke:#117abf,fill:none;

What’s changing in iPCV v2

Practitioner data consolidated into User In Role view for Simplification & Standardization

Views named ‘Active practitioner list’ & ‘practitioner’ are being be removed and merged into a single unified ‘User In Role’ view. All practitioner related information will now be available in a single unified User In Role view.

why?

The separation of practitioner data across 2 views was causing confusion for some customers, who expected to see all practitioners listed in the ‘practitioner’ view. By consolidating the data, we are simplifying the structure and making it easier to locate and use practitioner information.

Customer benefit

This change provides the following benefits:

Simplified output: No more toggling between multiple views or dealing with fragmented data to view practitioner information
Reduced confusion: No more need to reconcile or cross reference across multiple tables
Enhanced data accuracy: With a unified view, there’s less room for misinterpretation & oversight

Customer action

Customers should update their workflows and queries to reference the new unified ‘User In Role’ view instead of the ‘Active practitioner list’ and ‘practitioner’ views. This will ensure they access the consolidated and comprehensive practitioner information from a single source, reducing confusion and improving navigation.

Column updates

The User In Role model has been significantly restructured with the consolidation of Practitioner model data. Key changes include:

Removal of unnecessary fields that provided no value
Renaming of fields for better clarity and consistency
Addition of new fields to enhance integration capabilities
Separation of combined fields into individual columns for better data granularity

Following is a summary of columns from the previous Practitioner and User Following is a summary of columns from the previous Practitioner and User In Role model in iPCV v1 that have been removed or renamed.

Removals:

Field	Model	Explanation
`cdb`	Practitioner	organisation-grain - not required
`id`	Practitioner, User In Role	actually a guid to represent a user-in-role - not required
`employer_id`	Practitioner	an organisation’s national practice code - not required
`sds_role_profile_id`	Practitioner	the roles a practitioner is assigned to - not required

Renames:

Field	Model	Renamed To
`sds_job_role_code`	Practitioner	`job_category_code`
`local_id`	Practitioner	`user_id`
`local_job_role`	Practitioner	`job_category_name`
`hcp_type`	Practitioner	`healthcare_practitioner_type`
`gmp_number`	Practitioner	`general_medical_practitioner_number`
`forename`	Practitioner	`given_name`
`speciality`	Practitioner	`organisation_specialities`
`professional_identifier`	Practitioner	`general_medical_council_number`, `general_dental_council_number`, `nursing_and_midwifery_council_number`

Additions:

Field	Model	Explanation
`organisation_id`	Practitioner	Alternative identifier to organisation_guid for easier integration
`sds_user_id`	Practitioner	Spine Directory Service user identifier for NHS system integration
`emis_user_id`	Practitioner	User identifier in an organisation
`identifier_issuing_body`	Practitioner	Authority that issued professional identifiers for compliance tracking
`is_deleted`	User In Role	Added to indicate whether a user in role record has been marked as soft deleted

Why?

The Practitioner model shared common data columns and similar purpose with the User In Role model, making consolidation logical
Some fields like sds_role_profile_id had 98% null values, providing no meaningful data
Fields like professional_identifier combined multiple distinct identifiers, reducing data clarity
Customer feedback indicated preference for simpler identifier formats and better NHS system integration

Customer benefits

Improved consistency: Single unified model eliminates data fragmentation
Enhanced data accuracy: Separate columns for professional identifiers provide clearer data representation
Better integration: New fields like organisation_id, sds_user_id and emis_user_id where not exists to simplify system connections
Regulatory compliance: identifier_issuing_body field improves audit and validation capabilities
Simplified querying: Cleaner field names and structure reduce complexity
Enhanced Data Maintenance: The is_deleted column enables more effective management of deleted user in role records

Customer action required

Update workflows and queries to reference the unified ‘User In Role’ view instead of separate ‘Active practitioner list’ and ‘practitioner’ views
For new integrations, consider using organisation_id instead of organisation_guid for simpler integration
Review any logic that depends on the previous professional_identifier field structure and update to use the new separate identifier columns
No action required for new fields (sds_user_id, emis_user_id , identifier_issuing_body) unless enhanced integration capabilities are desired
Use the is_deleted column to manage and filter out deleted user in role records as needed

Examples

Get active user-in-roles

SELECT
  *
FROM hive.explorer_ipcv.srv_user_in_role
WHERE contract_end_date IS NULL

Get all users in a specific role

SELECT
  *
FROM hive.explorer_ipcv.srv_user_in_role
WHERE job_category_name = 'Example Role'

Get all roles pertaining to a single user

SELECT
  *
FROM hive.explorer_ipcv.srv_user_in_role
WHERE user_id = 123