Logical Model

There are four core tables in HSDS:

  1. organization - that provide services;

  2. service - that have descriptions, classifications and other information to allow potential service users to identify those services that can meet their needs;

  3. location - where services are delivered - either physically, or virtually (over the phone or internet);

  4. service_at_location - a link table used to record where particular services are available, and to override any default service or location information, with information specific to the service at a specific location.

Additional information about organizations, locations and services is held in separate tables and linked by a foreign key. Some tables only have a single foreign key for a single core table. Others can be linked to multiple core tables.

The table below indicates the foreign keys that link tables with the core tables in HSDS. Note that other links between non-core tables exist. See the entity relationship diagram below and reference page for more detail.

Table

organization

service

location

service_at_location

program

X

service

X

service_at_location

X

X

location

X

phone

X

X

X

X

contact

X

X

X

address

X

schedule

X

X

X

funding

X

X

service_taxonomy

X

eligibility

X

service_area

X

required_document

X

language

X

X

accessibility

X

cost_option

X

organization_identifier

X

When a single row contains multiple foreign keys, these must be interpreted as ‘or’ relationships. For example, a phone number applies to the service OR the organization OR the service_at_location.

The following tables do not have a foreign key for any of the core tables:

  • attribute can be joined to any table other than itself or metadata using _link_id, and to taxonomy_term using taxonomy_term_id.

  • taxonomy_term can be joined to taxonomy using taxonomy_id.

  • metadata can be joined with any table other than itself or attribute using resource_id.

Entity Relationship Diagram

[enlarge]

Changeme organization organization The organization record is used to provide basic description and details about each organization delivering services. Each service should be linked to the organization responsible for its delivery. One organization may deliver many services. id string UNIQ; Each organization must have a unique identifier. name string The official or public name of the organization. alternate_name string NULL ; Alternative or commonly used name for the organization. description string A brief summary about the organization. It can contain markup such as HTML or Markdown. email string NULL ; The contact e-mail address for the organization. website string NULL ; The URL (website address) of the organization. tax_status string NULL ; DEPRECATED: Government assigned tax designation for tax-exempt organizations. tax_id string NULL ; DEPRECATED: A government issued identifier used for the purpose of tax administration. year_incorporated number NULL ; The year in which the organization was legally formed. legal_status string NULL ; The legal status defines the conditions that an organization is operating under; e.g. non-profit, private corporation or a government organization. logo string NULL ; A URL to an image associated with the organization which can be presented alongside its name. uri string NULL ; A persistent identifier to uniquely identify the organization such as those provided by Open Corporates or some other relevant URI provider. This is not for listing the website of the organization: that can be done through the website field of the Organization. parent_organization_id string NULL ; The identifier of the organization's parent organization service service Services are provided by organizations to a range of different groups. Details on where each service is delivered are contained in the service_at_locations table. id string UNIQ; The identifier for the service. Each service must have a unique identifier. organization_id string The identifier of the organization that provides this service. program_id string NULL ; The identifier of the program this service is delivered under. name string The official or public name of the service. alternate_name string NULL ; If the service has an alternative or commonly used other name, it can be given here. description string NULL ; A description of the service. url string NULL ; URL of the service email string NULL ; An email address which can be used by WHOM? to contact the service provider. status string The current status of the service which can be active, inactive, defunct, or temporarily closed. interpretation_services string NULL ; A description of any interpretation services available for accessing this service. application_process string NULL ; A description of the steps needed to access this service. fees_description string NULL ; Details of any charges for service users to access this service. wait_time string NULL ; DEPRECATED: Time a client may expect to wait before receiving a service. fees string NULL ; DEPRECATED: Details of any charges for service users to access this service. accreditations string NULL ; Details of any accreditations. Accreditation is the formal evaluation of an organization or program against best practice standards set by an accrediting organization. eligibility_description string NULL ; A description of the type of person for whom this service is intended. minimum_age number NULL ; The minimum age of a person required to meet this eligibility requirement. maximum_age number NULL ; The maximum age of a person required to meet this eligibility requirement. assured_date date NULL ; The date that the information about the service was last checked assurer_email string NULL ; The contact e-mail address for the person or organization which last assured the service. licenses string NULL ; DEPRECATED: An organization may have a license issued by a government entity to operate legally. A list of any such licenses can be provided here. alert string NULL ; Notice of any short term alerts concerning the service. e.g. service:i2->organization:f1 location location The location tables provides details of the locations where organizations operate. Locations may be virtual, and one organization may have many locations. id string UNIQ; The identifier of the location. Each location must have a unique identifier. location_type string The type of location, which may be either physical, postal, or virtual. url string NULL ; If location_type is virtual, then this field represents the URL of a virtual location. organization_id string NULL ; The organization identifier for a location. This is the organization that is responsible for maintaining information about this location. The identifier of the organization should be given here. Details of the services the organization delivers at this location should be provided in the services_at_location table. name string NULL ; The name of the location alternate_name string NULL ; If the location has an alternative name then it may be given here. description string NULL ; A description of this location. transportation string NULL ; A description of the access to public or private transportation to and from the location. latitude number NULL ; Y coordinate of location expressed in decimal degrees in WGS84 datum. longitude number NULL ; X coordinate of location expressed in decimal degrees in WGS84 datum. external_identifier string NULL ; A third party identifier for a location which can be drawn from other services e.g. UK UPRN or what3words. external_identifier_type string NULL ; The scheme used for the location's external_identifier e.g. UK UPRN or what3words. location:i4->organization:f1

Entity Relationship Diagram (Full version)

Core tables are shown in green.

Other tables are shown in blue.

[enlarge]

Changeme organization organization The organization record is used to provide basic description and details about each organization delivering services. Each service should be linked to the organization responsible for its delivery. One organization may deliver many services. id string UNIQ; Each organization must have a unique identifier. name string The official or public name of the organization. alternate_name string NULL ; Alternative or commonly used name for the organization. description string A brief summary about the organization. It can contain markup such as HTML or Markdown. email string NULL ; The contact e-mail address for the organization. website string NULL ; The URL (website address) of the organization. tax_status string NULL ; DEPRECATED: Government assigned tax designation for tax-exempt organizations. tax_id string NULL ; DEPRECATED: A government issued identifier used for the purpose of tax administration. year_incorporated number NULL ; The year in which the organization was legally formed. legal_status string NULL ; The legal status defines the conditions that an organization is operating under; e.g. non-profit, private corporation or a government organization. logo string NULL ; A URL to an image associated with the organization which can be presented alongside its name. uri string NULL ; A persistent identifier to uniquely identify the organization such as those provided by Open Corporates or some other relevant URI provider. This is not for listing the website of the organization: that can be done through the website field of the Organization. parent_organization_id string NULL ; The identifier of the organization's parent organization program program Some organizations organise their services into programs. A program brings together a number of related services. id string UNIQ; The identifier for the program. Each program must have a unique identifier. organization_id string UNIQ; The identifier for the organization which the program belongs to. Each program must belong to a single organization, and the identifier for that organization should be given here. name string The name of the program alternate_name string NULL ; If the program has an alternative name it can be given here description string A brief and clear summary of the program program:i2->organization:f1 service service Services are provided by organizations to a range of different groups. Details on where each service is delivered are contained in the service_at_locations table. id string UNIQ; The identifier for the service. Each service must have a unique identifier. organization_id string The identifier of the organization that provides this service. program_id string NULL ; The identifier of the program this service is delivered under. name string The official or public name of the service. alternate_name string NULL ; If the service has an alternative or commonly used other name, it can be given here. description string NULL ; A description of the service. url string NULL ; URL of the service email string NULL ; An email address which can be used by WHOM? to contact the service provider. status string The current status of the service which can be active, inactive, defunct, or temporarily closed. interpretation_services string NULL ; A description of any interpretation services available for accessing this service. application_process string NULL ; A description of the steps needed to access this service. fees_description string NULL ; Details of any charges for service users to access this service. wait_time string NULL ; DEPRECATED: Time a client may expect to wait before receiving a service. fees string NULL ; DEPRECATED: Details of any charges for service users to access this service. accreditations string NULL ; Details of any accreditations. Accreditation is the formal evaluation of an organization or program against best practice standards set by an accrediting organization. eligibility_description string NULL ; A description of the type of person for whom this service is intended. minimum_age number NULL ; The minimum age of a person required to meet this eligibility requirement. maximum_age number NULL ; The maximum age of a person required to meet this eligibility requirement. assured_date date NULL ; The date that the information about the service was last checked assurer_email string NULL ; The contact e-mail address for the person or organization which last assured the service. licenses string NULL ; DEPRECATED: An organization may have a license issued by a government entity to operate legally. A list of any such licenses can be provided here. alert string NULL ; Notice of any short term alerts concerning the service. e.g. service:i2->organization:f1 service:i3->program:f1 attribute attribute The services attributes table creates a link between a service and one or more classifications that describe the nature of the service provided. id string UNIQ; The identifier of the service_attribute entry. Each service_attribute entry should have a unique identifier. link_id string The identifier of the entity to which this taxonomy term applies. taxonomy_term_id string The identifier of this taxonomy term from the taxonomy table. link_type string NULL ; A code taken from an enumerated open codelist to indicate what the taxonomy term describes e.g. the service eligibility or intended audience. link_entity string The table of the link_id value string NULL ; The value (if any) of an attribute taxonomy_term taxonomy_term Each service can be categorized according to one or more taxonomy terms. The taxonomy_term table contains a list of taxonomy terms, their names, and, for hierarchical taxonomies, their structure. id string UNIQ; The identifier for this taxonomy term. Each taxonomy term must have a unique identifier, within the scope of the dataset. code string NULL ; UNIQ; The term identfier as used in the taxonomy. This and the taxonomy_id combined define the term name string The taxonomy term itself. description string What the term means parent_id string NULL ; If this is a child term in a hierarchical taxonomy, give the identifier of the parent category. For top-level categories, this should be left blank. taxonomy string NULL ; If this is an established taxonomy, detail which taxonomy is in use. For example, AIRS or Open Eligibility. If possible, provide a URI. language string NULL ; An ISO 639-1, or ISO 639-2 [language code](available at http://www.loc.gov/standards/is o639-2/php/code_list.php) to represent the language of the term. The three-letter codes from ISO 639-2 provide greater accuracy when describing variants of languages, which may be relevant to particular communities. taxonomy_id string NULL ; The identifier of the taxonomy containing the term term_uri string NULL ; URI of the term attribute:i3->taxonomy_term:f1 taxonomy taxonomy Taxonomies from which taxonomy terms are taken id string UNIQ; Each entry must have a unique identifier name string The name of the taxonomy from which terms are sourced. description string Description of the taxonomy. uri string NULL ; The URI of the taxonomy. version string NULL ; The taxonomy's version. taxonomy_term:i8->taxonomy:f1 service_at_location service_at_location The services at location table creates a link between a service and a specific location. id string UNIQ; The identifier of the entry. Each entry must have a unique identifier. service_id string The identifier of the service at a given location. location_id string The identifier of the location where this service operates. description string NULL ; Any additional information that should be displayed to users about the service at this specific location. service_at_location:i2->service:f1 location location The location tables provides details of the locations where organizations operate. Locations may be virtual, and one organization may have many locations. id string UNIQ; The identifier of the location. Each location must have a unique identifier. location_type string The type of location, which may be either physical, postal, or virtual. url string NULL ; If location_type is virtual, then this field represents the URL of a virtual location. organization_id string NULL ; The organization identifier for a location. This is the organization that is responsible for maintaining information about this location. The identifier of the organization should be given here. Details of the services the organization delivers at this location should be provided in the services_at_location table. name string NULL ; The name of the location alternate_name string NULL ; If the location has an alternative name then it may be given here. description string NULL ; A description of this location. transportation string NULL ; A description of the access to public or private transportation to and from the location. latitude number NULL ; Y coordinate of location expressed in decimal degrees in WGS84 datum. longitude number NULL ; X coordinate of location expressed in decimal degrees in WGS84 datum. external_identifier string NULL ; A third party identifier for a location which can be drawn from other services e.g. UK UPRN or what3words. external_identifier_type string NULL ; The scheme used for the location's external_identifier e.g. UK UPRN or what3words. service_at_location:i3->location:f1 location:i4->organization:f1 phone phone The phone table contains details of the telephone numbers are used to contact organizations, services, and locations. id string UNIQ; The id for a phone number. Each entry must have a unique identifier. location_id string NULL ; The identifier of the location where this phone number is located service_id string NULL ; The identifier of the service for which this is the phone number organization_id string NULL ; The identifier of the organization for which this is the phone number contact_id string NULL ; The identifier of the contact for which this is the phone number service_at_location_id string NULL ; The identifier of the ‘service at location’ table entry, when this phone number is specific to a service in a particular location. number string The phone number extension number NULL ; The extension of the phone number type string NULL ; Indicates the type of phone service, drawing from the RFC6350 list of types (text (for SMS), voice, fax, cell, video, pager, textphone). description string NULL ; A description providing extra information about the phone service (e.g. any special arrangements for accessing, or details of availability at particular times. phone:i4->organization:f1 phone:i3->service:f1 phone:i6->service_at_location:f1 phone:i2->location:f1 contact contact The contact table contains details of the named contacts for services and organizations. Note that in the HSDS data package format, if an individual is the contact for multiple services, their details may be duplicated multiple times in this table, each time with a new identifier, and with the rows containing different service ids. id string UNIQ; The identifier for the contact. Each contact must have a unique identifier. organization_id string NULL ; The identifier of the organization for which this is a contact service_id string NULL ; The identifier of the service for which this is a contact service_at_location_id string NULL ; The identifier of the ‘service at location’ table entry, when this contact is specific to a service in a particular location. location_id string NULL ; The identifier for the location_id when the location of the contact is relevant. name string NULL ; The name of the contact. title string NULL ; The job title of the contact. department string NULL ; The department that the contact is a part of. email string NULL ; The email address of the contact. phone:i5->contact:f1 contact:i2->organization:f1 contact:i3->service:f1 contact:i4->service_at_location:f1 contact:i5->location:f1 address address Physical or postal address id string UNIQ; The id of the postal address. Each postal address must have a unique identifier. location_id string NULL ; The identifier of the location for which this is the postal address. attention string NULL ; The name of the person or entity whose attention should be sought at the location. These are often included as a "care of" component of an address. address_1 string The first line(s) of the address, including office, building number and street. address_2 string NULL ; A second (additional) line of address information. city string The city in which the address is located. region string NULL ; The region in which the address is located (optional). state_province string The state or province in which the address is located. postal_code string The postal code for the address. country string The country in which the address is located. This should be given as an ISO 3361-1 country code (two letter abbreviation) address_type string The type of address which may be physical, postal, or virtual. address:i2->location:f1 schedule schedule The schedule table contains details of when a service or location is open. Entries are RFC 5545 RRULES. id string UNIQ; The identifier for the schedule. Each entry must have a unique identifier. service_id string NULL ; The identifier of the service for which this is the regular schedule location_id string NULL ; The identifier of the location for which this is the regular schedule service_at_location_id string NULL ; The identifier of the ‘service at location’ table entry, when this schedule is specific to a service in a particular location. valid_from date NULL ; The date from which the schedule information is valid. It must be in the ISO 8601 format of YYYY-MM-DD valid_to date NULL ; The last date on which the schedule information is valid. It must be in the ISO 8601 format of YYYY-MM-DD dtstart date NULL ; iCal - The date of the first event is the schedule. Necessary when using the ‘interval’ feature, optional otherwise. timezone number NULL ; The timezone that all dates are expressed as, expressed as a UTC offset. Dates are assumed to be UTC otherwise. until date NULL ; iCal - The date of the last occurence of the recurring event. count date NULL ; iCal - The number of times that the event occurs. Use this instead of ‘until’, if appropriate. wkst string NULL ; iCal - The day on which the week starts, eg MO. freq string NULL ; iCal - How often the frequency repeats. E.g. Frequency use one of WEEKLY or MONTHLY. interval number NULL ; iCal - How often the frequency repeats. E.g. Interval 2 for Frequency WEEKLY gives Fortnightly byday string NULL ; iCal - Comma seperated days of the week. E.g. SU,MO,TU,WE,TH,FR,SA. Where freq is MONTHLY each part can be preceded by a positive or negative integer to represent which occurrence in a month; e.g. 2MO is the second Monday in a month. -1FR is the last Friday byweekno string NULL ; iCal - Comma seperated numeric weeks of the year if freq is WEEKLY. Can be negative to represent weeks before the end of the year; e.g. -5 is the 5th to last week in a year. bymonthday number NULL ; iCal - Comma separated numeric days of the month if frequency is MONTHLY. Can be negative to represent days before the end of the month; e.g. -5 is the 5th to last day in a month. byyearday number NULL ; iCal - Comma separated numeric days of the month if frequency is YEARLY. Can be negative to represent days before the end of the year; e.g. -1 is the last day in a year. description string NULL ; A human readable description of the availability of the service. opens_at time NULL ; The time when a service or location opens. This should use HH:MM format and should include timezone information, either adding the suffix ‘Z’ when the date is in UTC, or including an offset from UTC (e.g. 09:00-05:00 for 9am EST.) closes_at time NULL ; The time when a service or location closes. This should use HH:MM format and should include timezone information, either adding the suffix ‘Z’ when the date is in UTC, or including an offset from UTC (e.g. 09:00-05:00 for 9am EST.) schedule_link string NULL ; URL of a link for the scheule which may show each individual session and may provide a booking facility attending_type string NULL ; A clear description of how to attend this service notes string NULL ; Human readable notes on the schedule schedule:i2->service:f1 schedule:i4->service_at_location:f1 schedule:i3->location:f1 funding funding The funding table describes the sources of funding for a service or organization id string UNIQ; The identifier for the funding. Each entry must have a unique identifier. organization_id string NULL ; The identifier of the organization in receipt of this funding. service_id string NULL ; The identifier of the service in receipt of this funding source string NULL ; A free text description of the source of funds for this organization or service. funding:i2->organization:f1 funding:i3->service:f1 service_area service_area The service_area table contains details of the geographic area for which a service is available. id string UNIQ; The identifier for the service area. Each service area must have a unique identifier. service_id string NULL ; The identifier of the service for which this entry describes the service area name string NULL ; The geographic area where a service is available. This is a free-text description, and so may be precise or indefinite as necessary. description string NULL ; A more detailed description of this service area. Used to provide any additional information that cannot be communicated using the structured area and geometry fields. extent string NULL ; A definition of the polygon defining the area, in geojson. extent_type string NULL ; The format of the extent field populated from an enum of  "geojson", "topojson", "kml",and (for legacy systems or early state during transformation) "text" uri string NULL ; A URI which acts as a persistent identifier to identifiy an area service_area:i2->service:f1 required_document required_document The required_document table contains details of any documents that are required in order to access or use services. id string UNIQ; The identifier for the document. Each document must have a unique identifier. service_id string NULL ; The identifier of the service for which this entry describes the required document document string NULL ; A clear description of the document required to apply for or receive the service e.g. "A Government-issued ID" or "EU Passport". uri string NULL ; Web link to document. required_document:i2->service:f1 language language The language table contains details of the languages that are spoken at locations or services. This does not include languages which can only be used with interpretation. id string UNIQ; The identifier for the language. Each langauge must have a unique identifier. service_id string NULL ; The identifier of the service for which the entry describes the languages in which services are delivered location_id string NULL ; The identifier of the location for which the entry describes the languages in which services are delivered phone_id string NULL ; The identifier of the phone for which the entry describes the languages in which phone is availible in. name string NULL ; Languages, other than English, in which the service is delivered. Languages are listed as ISO639-1 codes. code string NULL ; The ISO 639-1 or ISO 639-3 code for the language e.g. en, es note string NULL ; A clear description of any additional context or services provided for this language e.g. an occasional translation service language:i2->service:f1 language:i3->location:f1 language:i4->phone:f1 accessibility accessibility The accessibility_for_disabilities table contains details of the arrangements for access to locations for people who have disabilities id string UNIQ; The identifier for this accessibility information. Each entry must have a unique identifier. location_id string NULL ; The identifier of the location for which the entry describes the accessibility provision description string NULL ; Description of assistance or infrastructure that facilitate access to clients with disabilities. details string NULL ; Any further details relating to the relevant accessibility arrangements at this location. E.g. whether advance notice is required to use an accessibility facility. url string NULL ; URL of a page giving more information about the accessibility of the location accessibility:i2->location:f1 metadata metadata The metadata table contains a record of the changes that have been made to the data in order to maintain provenance information. id string UNIQ; Each entry must have a unique identifier resource_id string Each service, program. location, address, or contact will have a unique identifier. resource_type string The type of entity being referenced last_action_date datetime The date when data was changed. last_action_type string The kind of change made to the data; eg create, update, delete field_name string The name of field that has been modified previous_value string The previous value of a field that has been updated replacement_value string The new value of a field that has been updated updated_by string The name of the person who updated a value meta_table_description meta_table_description The meta_table_description table contains metadata about individual tables. id string UNIQ; Each entry must have a unique identifier name string NULL language string NULL character_set string NULL cost_option cost_option The cost option table contains the costs of a service at certain points in time. This is an LGA Extension table. This table provides a structured version of the text information contained in the 'fees' field of the 'service' table. id string UNIQ; Each entry must have a unique identifier service_id string The identifier of the services for which the entry describes the cost valid_from date NULL ; When this price is valid from valid_to date NULL ; When this price is valid to option string NULL ; Conditions associated with the cost option.; ; Application Profile Notes: e.g.For the over 60's; 1st session currency string NULL ; 3 letter currency code amount number NULL ; The cost of the option, expresssed as an amount in a currency (expected to be GBP by Open Referral UK). amount_description string NULL ; Specific details qualifying the cost amount, e.g. 'per session' cost_option:i2->service:f1 organization_identifier organization_identifier Identifiers for an organization such as: company registration number, charity number, tax registration number. This helps uniquely identify and de-duplicate data id string UNIQ; Each entry must have a unique identifier organization_id string Each program must belong to a single organization. The identifier of the organization should be given here. identifier_scheme string NULL ; Scheme according to http://org- id.guide/. identifier_type string The type of identifier (company, charity, tax number, etc) identifier string The third-party identifier organization_identifier:i2->organization:f1