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 schema 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 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; The identifier for the organization. Each organization must have a unique identifier. name string The official or public name of the organization. alternate_name string NULL ; An (optional) alternative or commonly used name for the organization. description string A free text description containing 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 conditions that an organization is operating under. 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 The details of the services provided by organizations to a range of different groups. 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 ; An (optional) alternative name for this service. description string NULL ; A free text description of the service. url string NULL ; URL of the service email string NULL ; An email address which can be used 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 free text description of any interpretation services available for accessing this service. application_process string NULL ; A free text description of the steps needed to access this service. fees_description string NULL ; A free text description of any charges for service users to access this service. wait_time string NULL ; DEPRECATED: The 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 ; A free text description 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 free text 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 ; A description of any short term alerts concerning the service. last_modified datetime NULL ; The datetime when the service, or any related information about the service, has changed. Should have millisecond accuracy. service:i2->organization:f1 location location 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 ; An (optional) alternative name of the location. description string NULL ; A free text description of the location. transportation string NULL ; A free text description of the access to public or private transportation to and from the location. latitude number NULL ; The latitude of the location expressed in decimal degrees in WGS84 datum. longitude number NULL ; The longitude of the location expressed in decimal degrees in WGS84 datum. external_identifier string NULL ; A third party identifier for the location, which can be drawn from other services e.g. UK UPRN. external_identifier_type string NULL ; The scheme used for the location's external_identifier e.g. UK UPRN. location:i4->organization:f1

[download]

Entity Relationship Diagram (Full version)

Core tables are shown in green.

Other tables are shown in blue.

[enlarge]

Changeme organization organization The 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; The identifier for the organization. Each organization must have a unique identifier. name string The official or public name of the organization. alternate_name string NULL ; An (optional) alternative or commonly used name for the organization. description string A free text description containing 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 conditions that an organization is operating under. 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 The details of collections 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 ; The (optional) alternative name for the program. description string A free text description of the program program:i2->organization:f1 service service The details of the services provided by organizations to a range of different groups. 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 ; An (optional) alternative name for this service. description string NULL ; A free text description of the service. url string NULL ; URL of the service email string NULL ; An email address which can be used 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 free text description of any interpretation services available for accessing this service. application_process string NULL ; A free text description of the steps needed to access this service. fees_description string NULL ; A free text description of any charges for service users to access this service. wait_time string NULL ; DEPRECATED: The 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 ; A free text description 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 free text 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 ; A description of any short term alerts concerning the service. last_modified datetime NULL ; The datetime when the service, or any related information about the service, has changed. Should have millisecond accuracy. service:i2->organization:f1 service:i3->program:f1 attribute attribute 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 attribute entry. Each 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 Identifier. 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 object 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 A free text description of the term. 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 is not required. taxonomy string NULL ; If this is an established taxonomy, a free text description of which taxonomy is in use. 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 The taxonomies from which taxonomy terms are taken. id string UNIQ; The identifier of the taxonomy. Each entry must have a unique identifier name string The name of the taxonomy from which terms are sourced. description string A free text description of the taxonomy. uri string NULL ; The URI of the taxonomy. version string NULL ; The version of the taxonomy. taxonomy_term:i8->taxonomy:f1 service_at_location service_at_location A link between a service and a specific location. id string UNIQ; The identifier of the service at location 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 ; A free text description of the service at this specific location. service_at_location:i2->service:f1 location location 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 ; An (optional) alternative name of the location. description string NULL ; A free text description of the location. transportation string NULL ; A free text description of the access to public or private transportation to and from the location. latitude number NULL ; The latitude of the location expressed in decimal degrees in WGS84 datum. longitude number NULL ; The longitude of the location expressed in decimal degrees in WGS84 datum. external_identifier string NULL ; A third party identifier for the location, which can be drawn from other services e.g. UK UPRN. external_identifier_type string NULL ; The scheme used for the location's external_identifier e.g. UK UPRN. service_at_location:i3->location:f1 location:i4->organization:f1 phone phone The details of the telephone numbers used to contact organizations, services, and locations. id string UNIQ; The identifier for the 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 free text description providing extra information about the phone service phone:i4->organization:f1 phone:i3->service:f1 phone:i6->service_at_location:f1 phone:i2->location:f1 contact contact The details of the named contacts for services and organizations. 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’ entry, when this contact is specific to a service in a particular location. location_id string NULL ; The identifier for the location of the contact. 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 The addresses of locations where organizations operate. id string UNIQ; The identifier of the postal address. Each postal address must have a unique identifier. location_id string NULL ; The identifier of the location for this 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 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 occurrence of the recurring event. count number NULL ; iCal - The number of times that the event occurs. Use this instead of ‘until’, if appropriate. wkst string NULL ; iCal - The two-letter code for the day on which the week starts. freq string NULL ; iCal - How often the frequency repeats. interval number NULL ; iCal - How often the frequency repeats. For example, and Interval of 2 for a WEEKLY Frequency would represent fortnightly. byday string NULL ; iCal - Comma separated days of the week. 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 separated numeric weeks of the year, where 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 string NULL ; iCal - Comma separated numeric days of the month, where 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 string NULL ; iCal - Comma separated numeric days of the month, where 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 free text 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 schedule which may show each individual session and may provide a booking facility. attending_type string NULL ; A free text description of how to attend this service. notes string NULL ; Free text notes on the schedule. schedule:i2->service:f1 schedule:i4->service_at_location:f1 schedule:i3->location:f1 funding funding 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 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 ; A free text geographic area where a service is available. description string NULL ; A more detailed free text 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. 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 identify an area. service_area:i2->service:f1 required_document required_document The 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 free text description of the document required to apply for or receive the service. uri string NULL ; A web link to the document. required_document:i2->service:f1 language language 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 entry 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 services delivered. name string NULL ; The name of the language in which the service is delivered. code string NULL ; The ISO 639-1 or ISO 639-3 code for the language. note string NULL ; A free text description of any additional context or services provided for this language. language:i2->service:f1 language:i3->location:f1 language:i4->phone:f1 accessibility accessibility The 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 for the location of the accessibility provision. description string NULL ; A free text description of the assistance or infrastructure that facilitates access to clients with disabilities. details string NULL ; Any further details relating to the relevant accessibility arrangements at this location. url string NULL ; The URL of a page giving more information about the accessibility of the location. accessibility:i2->location:f1 metadata metadata A record of the changes that have been made to the data in order to maintain provenance information. id string UNIQ; The identifier for this metadata. Each entry must have a unique identifier. resource_id string The identifier of the resource (service, program, location, address, or contact) that this metadata describes. resource_type string The type of entity being referenced. last_action_date date The date when data was changed. last_action_type string The kind of change made to the data. field_name string The name of field that has been modified. previous_value string The previous value of the field that has been modified. replacement_value string The new value of the field that has been modified. updated_by string The name of the person who modified the field. meta_table_description meta_table_description Metadata about individual tables. id string UNIQ; The identifier for the metadata description. Each entry must have a unique identifier. name string NULL ; The name for the metadata description. language string NULL ; The ISO 639-1 or ISO 639-3 code for the language of the metadata description. character_set string NULL ; The character set of the metadata description. cost_option cost_option The costs of a service at certain points in time. This is an LGA Extension. This object provides a structured version of the text information contained in the 'fees' field of the 'service' object. id string UNIQ; The identifier for the cost option. 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 ; The date when this price is valid from. valid_to date NULL ; The date when this price is valid to. option string NULL ; Conditions associated with the cost option. currency string NULL ; The 3 letter currency code of this cost option (expected to be gbp by Open Referral UK). amount number NULL ; The cost of the option, expressed as an amount. amount_description string NULL ; Specific details qualifying the cost amount. cost_option:i2->service:f1 organization_identifier organization_identifier The details of the third party identifiers for organizations, such as: company registration number, charity number, tax registration number. id string UNIQ; The identifier for this organization identifier entry. Each entry must have a unique identifier. organization_id string The identifier of the organization. This should match the uuid of an organization object. identifier_scheme string NULL ; The scheme of the third party identifier, according to http://org-id.guide/. identifier_type string A human-readable equivalent of the identifier_scheme. This may be used in cases where org-id.guide does not list an appropriate identifier scheme. identifier string The third-party identifier value. organization_identifier:i2->organization:f1

[download]