Definition

The Organisation Location data model represents a bridge table that links organisations with their physical locations. This model enables efficient identification of an organisation’s main location and supports organisations operating across multiple sites.

Information

The model consolidates organisation and location relationships, with clear flagging of primary locations, providing a structured and accessible view of organisation location data.

Overview

graph TD
  A[Organisation]
  B[Location]

  A --> D[Organisation_Location]
  B --> D

  classDef nodeStyle stroke:#9961a4;
  class A,B,D nodeStyle;

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

What’s changing in iPCV v2

Complete organisation-location dataset

The Organisation Location model is a new bridge model in iPCV v2 that links organisations with all their physical locations. The model uses organisation_id, location_id, and location_guid columns to establish these relationships, with the is_main_location flag identifying the primary location for each organisation.

Why?

In iPCV v1, the organisation model only included the main location for each organisation. iPCV v2 now provides a complete dataset of all locations associated with an organisation, not just the primary one.

Customer benefit

• Complete visibility of all locations for each organisation through organisation_id and location_id relationships
• Clear identification of the primary location using the is_main_location flag
• Cleaner data structure with location details separated from organisation details
• More flexible querying capabilities using location_guid when location-specific information is needed
• Reduced data duplication and improved data integrity
• Better support for organisations operating across multiple sites

Customer action

• Update queries and reports to use the Organisation Location model when needing to access location information for organisations
• Review and update any applications or processes that assumed a single location per organisation to handle multiple locations
• Implement filtering logic to distinguish between main and additional locations using the is_main_location flag

Schema updates for data maintenance

Several schema enhancements have been introduced to improve data quality and organisational structure:

• The is_deleted column indicates whether an organisation location has been marked as deleted
• The is_main_location column identifies the primary location for an organisation
• The emis_main_location_guid column has been removed to accommodate multiple physical locations per organisation

Why?

These changes address the need for better data maintenance and support for organisations with multiple locations, while eliminating redundancy in location management.

Customer benefit

• Enhanced data maintenance through the is_deleted flag, enabling identification and management of obsolete organisation locations
• Clear identification of primary locations via the is_main_location flag, improving data clarity and query efficiency
• Complete location datasets for organisations with multiple sites
• Improved data quality through better lifecycle management of location records

Customer action

• Update ETL processes to filter out deleted organisation locations using the is_deleted column
• Leverage the is_main_location flag when querying for primary organisation locations
• Review and adjust any processes that previously relied on emis_main_location_guid

Examples

Get main organisation location

SELECT
  *
FROM hive.explorer_ipcv.srv_organisation_location
WHERE is_main_location = TRUE

Find deleted organisation locations

SELECT
  *
FROM hive.explorer_ipcv.srv_organisation_location
WHERE is_deleted = TRUE

Get organisation with location details

SELECT
  *
FROM hive.explorer_ipcv.srv_organisation organisation
JOIN
  hive.explorer_ipcv.srv_organisation_location organisation_location
    ON organisation.organisation_guid = organisation_location.organisation_guid