# Care Quality Commission schema ERD

Table and field reference for the Fivetran Care Quality Commission connector.

## Table & Field Reference

### location_type

| Column | Type | Keys |
| --- | --- | --- |
| type | Unknown | PK |
| location_id | Unknown | PK, FK |

### location_relationship

| Column | Type | Keys |
| --- | --- | --- |
| location_id | Unknown | PK, FK |
| related_location_id | Unknown | PK, FK |
| reason | Unknown |  |
| related_location_name | Unknown |  |
| type | Unknown |  |

### location_regulated_activity_contact

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| location_id | Unknown | PK, FK |
| location_regulated_activity_code | Unknown | PK, FK |
| person_family_name | Unknown |  |
| person_given_name | Unknown |  |
| person_roles | Json |  |
| person_title | Unknown |  |

### location_regulated_activity

| Column | Type | Keys |
| --- | --- | --- |
| code | Unknown | PK |
| location_id | Unknown | PK, FK |
| name | Unknown |  |

### location_gac_service_type

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| location_id | Unknown | PK, FK |
| description | Unknown |  |
| name | Unknown |  |

### location_inspection_category

| Column | Type | Keys |
| --- | --- | --- |
| code | Unknown | PK, FK |
| location_id | Unknown | PK, FK |
| is_primary | Unknown |  |
| name | Unknown |  |

### location_specialism

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| location_id | Unknown | PK, FK |

### location_inspection_area

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK, FK |
| location_id | Unknown | PK, FK |

### location_current_overall_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| location_id | Unknown | PK, FK |
| report_link_id | Unknown | FK |
| organisation_id | Unknown |  |
| rating | Unknown |  |
| report_date | Unknown |  |

### location_current_service_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| location_current_service_rating_name | Unknown | PK, FK |
| location_id | Unknown | PK, FK |
| rating | Unknown |  |

### location_current_service_rating

| Column | Type | Keys |
| --- | --- | --- |
| location_id | Unknown | PK, FK |
| name | Unknown | PK, FK |
| report_link_id | Unknown | FK |
| organisation_id | Unknown |  |
| rating | Unknown |  |
| report_date | Unknown |  |

### location_historic_overall_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| location_historic_rating_index | Unknown | PK, FK |
| location_id | Unknown | PK, FK |
| rating | Unknown |  |

### location_historic_service_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| location_historic_service_rating_index | Unknown | PK, FK |
| location_historic_service_rating_name | Unknown | PK, FK |
| location_id | Unknown | PK, FK |
| rating | Unknown |  |

### location_historic_service_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| location_historic_rating_index | Unknown | PK, FK |
| location_id | Unknown | PK, FK |
| rating | Unknown |  |

### location_historic_rating

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| location_id | Unknown | PK, FK |
| report_link_id | Unknown | FK |
| organisation_id | Unknown |  |
| overall_combined_quality_rating | Unknown |  |
| overall_combined_quality_summary | Unknown |  |
| overall_rating | Unknown |  |
| overall_use_of_resources_rating | Unknown |  |
| overall_use_of_resources_summary | Unknown |  |
| report_date | Unknown |  |

### location_report_related_document

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| location_id | Unknown | PK, FK |
| location_report_link_id | Unknown | PK, FK |
| document_type | Unknown |  |
| document_uri | Unknown |  |

### location_report

| Column | Type | Keys |
| --- | --- | --- |
| link_id | Unknown | PK |
| location_id | Unknown | PK, FK |
| first_visit_date | Unknown |  |
| report_date | Unknown |  |
| report_type | Unknown |  |
| report_uri | Unknown |  |

### location

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| brand_id | Unknown |  |
| brand_name | Unknown |  |
| care_home | Unknown |  |
| constituency | Unknown |  |
| current_overall_combined_quality_rating | Unknown |  |
| current_overall_combined_quality_summary | Unknown |  |
| current_overall_rating | Unknown |  |
| current_overall_report_date | Unknown |  |
| current_overall_report_link_id | Unknown |  |
| current_overall_use_of_resources_rating | Unknown |  |
| current_overall_use_of_resources_summary | Unknown |  |
| dormancy | Unknown |  |
| inspection_directorate | Unknown |  |
| last_inspection_date | Unknown |  |
| last_report_publication_date | Unknown |  |
| local_authority | Unknown |  |
| main_phone_number | Unknown |  |
| name | Unknown |  |
| number_of_beds | Unknown |  |
| ods_ccg_code | Unknown |  |
| ods_ccg_name | Unknown |  |
| ods_code | Unknown |  |
| onspd_ccg_code | Unknown |  |
| onspd_ccg_name | Unknown |  |
| onspd_icb_code | Unknown |  |
| onspd_icb_name | Unknown |  |
| onspd_latitude | Unknown |  |
| onspd_longitude | Unknown |  |
| organisation_type | Unknown |  |
| postal_address_line_1 | Unknown |  |
| postal_address_town_city | Unknown |  |
| postal_code | Unknown |  |
| provider_id | Unknown |  |
| region | Unknown |  |
| registered_manager_absent_date | Unknown |  |
| registration_date | Unknown |  |
| registration_status | Unknown |  |
| type | Unknown |  |
| uprn | Unknown |  |

### inspection_category

| Column | Type | Keys |
| --- | --- | --- |
| code | Unknown | PK |
| inspection_area_id | Unknown | PK, FK |
| core_flag | Unknown |  |
| end_date | Unknown |  |
| name | Unknown |  |
| org_inspection_area_retirement_date | Unknown |  |
| status | Unknown |  |

### inspection_area

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| end_date | Unknown |  |
| name | Unknown |  |
| status | Unknown |  |
| superseded_by | Json |  |
| type | Unknown |  |

### provider_contact

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| provider_id | Unknown | PK, FK |
| person_family_name | Unknown |  |
| person_given_name | Unknown |  |
| person_roles | Json |  |
| person_title | Unknown |  |

### provider_location

| Column | Type | Keys |
| --- | --- | --- |
| location_id | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |

### provider_relationship

| Column | Type | Keys |
| --- | --- | --- |
| provider_id | Unknown | PK, FK |
| related_provider_id | Unknown | PK, FK |
| reason | Unknown |  |
| related_provider_name | Unknown |  |
| type | Unknown |  |

### provider_regulated_activity

| Column | Type | Keys |
| --- | --- | --- |
| code | Unknown | PK |
| provider_id | Unknown | PK, FK |
| name | Unknown |  |
| nominated_individual_person_family_name | Unknown |  |
| nominated_individual_person_given_name | Unknown |  |
| nominated_individual_person_title | Unknown |  |

### provider_inspection_area

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |

### provider_inspection_category

| Column | Type | Keys |
| --- | --- | --- |
| code | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |
| is_primary | Unknown |  |
| name | Unknown |  |

### provider_historic_service_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| provider_historic_service_rating_index | Unknown | PK, FK |
| provider_historic_service_rating_name | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |
| rating | Unknown |  |

### provider_historic_service_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| provider_historic_rating_index | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |
| rating | Unknown |  |

### provider_historic_overall_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| provider_historic_rating_index | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |
| rating | Unknown |  |

### provider_historic_rating

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| provider_id | Unknown | PK, FK |
| report_link_id | Unknown | FK |
| organisation_id | Unknown |  |
| overall_combined_quality_rating | Unknown |  |
| overall_combined_quality_summary | Unknown |  |
| overall_rating | Unknown |  |
| overall_use_of_resources_rating | Unknown |  |
| overall_use_of_resources_summary | Unknown |  |
| report_date | Unknown |  |

### provider_report_related_document

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| provider_id | Unknown | PK, FK |
| provider_report_link_id | Unknown | PK, FK |
| document_type | Unknown |  |
| document_uri | Unknown |  |

### provider_report

| Column | Type | Keys |
| --- | --- | --- |
| link_id | Unknown | PK |
| provider_id | Unknown | PK, FK |
| report_date | Unknown |  |
| report_type | Unknown |  |
| report_uri | Unknown |  |

### provider_current_overall_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| provider_id | Unknown | PK, FK |
| report_link_id | Unknown | FK |
| rating | Unknown |  |
| report_date | Unknown |  |

### provider_current_service_key_question_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| provider_current_service_rating_name | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |
| rating | Unknown |  |

### provider_current_service_rating

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK, FK |
| provider_id | Unknown | PK, FK |
| report_link_id | Unknown | FK |
| organisation_id | Unknown |  |
| rating | Unknown |  |
| report_date | Unknown |  |

### provider

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| brand_id | Unknown |  |
| brand_name | Unknown |  |
| charity_number | Unknown |  |
| companies_house_number | Unknown |  |
| constituency | Unknown |  |
| current_overall_combined_quality_rating | Unknown |  |
| current_overall_combined_quality_summary | Unknown |  |
| current_overall_rating | Unknown |  |
| current_overall_report_date | Unknown |  |
| current_overall_report_link_id | Unknown |  |
| current_overall_use_of_resources_rating | Unknown |  |
| current_overall_use_of_resources_summary | Unknown |  |
| inspection_directorate | Unknown |  |
| last_inspection_date | Unknown |  |
| last_report_publication_date | Unknown |  |
| local_authority | Unknown |  |
| main_phone_number | Unknown |  |
| name | Unknown |  |
| ods_code | Unknown |  |
| onspd_icb_code | Unknown |  |
| onspd_icb_name | Unknown |  |
| onspd_latitude | Unknown |  |
| onspd_longitude | Unknown |  |
| organisation_type | Unknown |  |
| ownership_type | Unknown |  |
| postal_address_county | Unknown |  |
| postal_address_line_1 | Unknown |  |
| postal_address_line_2 | Unknown |  |
| postal_address_town_city | Unknown |  |
| postal_code | Unknown |  |
| region | Unknown |  |
| registration_date | Unknown |  |
| registration_status | Unknown |  |
| type | Unknown |  |
| uprn | Unknown |  |
| website | Unknown |  |

## Relationships

- location_type(location_id) → location(id)
- location_relationship(related_location_id) → location(id)
- location_relationship(location_id) → location(id)
- location_regulated_activity_contact(location_regulated_activity_code) → location_regulated_activity(code)
- location_regulated_activity_contact(location_id) → location_regulated_activity(location_id)
- location_regulated_activity(location_id) → location(id)
- location_gac_service_type(location_id) → location(id)
- location_inspection_category(code) → inspection_category(code)
- location_inspection_category(location_id) → location(id)
- location_specialism(location_id) → location(id)
- location_inspection_area(id) → inspection_area(id)
- location_inspection_area(location_id) → location(id)
- location_current_overall_key_question_rating(report_link_id) → location_report(link_id)
- location_current_overall_key_question_rating(location_id) → location(id)
- location_current_service_key_question_rating(location_current_service_rating_name) → location_current_service_rating(name)
- location_current_service_key_question_rating(location_id) → location_current_service_rating(location_id)
- location_current_service_rating(name) → location_inspection_area(id)
- location_current_service_rating(report_link_id) → location_report(link_id)
- location_current_service_rating(location_id) → location(id)
- location_historic_overall_key_question_rating(location_historic_rating_index) → location_historic_rating(index)
- location_historic_overall_key_question_rating(location_id) → location_historic_rating(location_id)
- location_historic_service_key_question_rating(location_historic_service_rating_name) → location_historic_service_rating(name)
- location_historic_service_key_question_rating(location_historic_service_rating_index) → location_historic_service_rating(location_historic_rating_index)
- location_historic_service_key_question_rating(location_id) → location_historic_service_rating(location_id)
- location_historic_service_rating(location_historic_rating_index) → location_historic_rating(index)
- location_historic_service_rating(location_id) → location_historic_rating(location_id)
- location_historic_rating(report_link_id) → location_report(link_id)
- location_historic_rating(location_id) → location(id)
- location_report_related_document(location_report_link_id) → location_report(link_id)
- location_report_related_document(location_id) → location_report(location_id)
- location_report(location_id) → location(id)
- inspection_category(inspection_area_id) → inspection_area(id)
- provider_contact(provider_id) → provider(id)
- provider_location(location_id) → location(id)
- provider_location(provider_id) → provider(id)
- provider_relationship(related_provider_id) → provider(id)
- provider_relationship(provider_id) → provider(id)
- provider_regulated_activity(provider_id) → provider(id)
- provider_inspection_area(id) → inspection_area(id)
- provider_inspection_area(provider_id) → provider(id)
- provider_inspection_category(code) → inspection_category(code)
- provider_inspection_category(provider_id) → provider(id)
- provider_historic_service_key_question_rating(provider_historic_service_rating_name) → provider_historic_service_rating(name)
- provider_historic_service_key_question_rating(provider_historic_service_rating_index) → provider_historic_service_rating(provider_historic_rating_index)
- provider_historic_service_key_question_rating(provider_id) → provider_historic_service_rating(provider_id)
- provider_historic_service_rating(provider_historic_rating_index) → provider_historic_rating(index)
- provider_historic_service_rating(provider_id) → provider_historic_rating(provider_id)
- provider_historic_overall_key_question_rating(provider_historic_rating_index) → provider_historic_rating(index)
- provider_historic_overall_key_question_rating(provider_id) → provider_historic_rating(provider_id)
- provider_historic_rating(report_link_id) → provider_report(link_id)
- provider_historic_rating(provider_id) → provider(id)
- provider_report_related_document(provider_report_link_id) → provider_report(link_id)
- provider_report_related_document(provider_id) → provider_report(provider_id)
- provider_report(provider_id) → provider(id)
- provider_current_overall_key_question_rating(report_link_id) → provider_report(link_id)
- provider_current_overall_key_question_rating(provider_id) → provider(id)
- provider_current_service_key_question_rating(provider_current_service_rating_name) → provider_current_service_rating(name)
- provider_current_service_key_question_rating(provider_id) → provider_current_service_rating(provider_id)
- provider_current_service_rating(name) → provider_inspection_area(id)
- provider_current_service_rating(report_link_id) → provider_report(link_id)
- provider_current_service_rating(provider_id) → provider(id)