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 over-ride 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 different core tables.

The table below indicates the foreign keys that exist.

Table

organization

service

location

service_at_location

program

X

service

X

service_attribute

X

service_at_location

X

X

location

X

phone*

X

X

X

X

contact

X

X

X

physical_address

X

postal_address

X

schedule

X

X

X

funding

X

X

service_taxonomy

X

eligibility

X

service_area

X

required_document

X

payment_accepted

X

language

X

X

accessibility_for_disabilities

X

*Phone can also be linked to contact.

When a single row contains multiple foreign keys, these must be interpreted as ‘OR’ relationships.

E.g. a phone number applies to the service OR the organisation OR the service_at_location.

ER 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. url string NULL ; The URL (website address) of the organization. tax_status string NULL ; Government assigned tax designation for tax-exempt organizations. tax_id string NULL ; A government issued identifier used for the purpose of tax administration. year_incorporated date 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. service service Services are provided by organizations to a range of different groups. Details on where each service is delivered are contained in the services_at_location table. id string UNIQ; 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 ; Alternative or commonly used name for a service. description string NULL ; A description of the service. url string NULL ; URL of the service email string NULL ; Email address for the service status string The current status of the service. interpretation_services string NULL ; A description of any interpretation services available for accessing this service. application_process string NULL ; The steps needed to access the service. wait_time string NULL ; Time a client may expect to wait before receiving a service. fees string NULL ; 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. licenses string NULL ; An organization may have a license issued by a government entity to operate legally. A list of any such licenses can be provided here. 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; Each location must have a unique identifier organization_id string Each location entry should be linked to a single organization. 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 The name of the location alternate_name string An alternative name for the location description string NULL ; A description of this location. transportation string A description of the access to public or private transportation to and from the location. latitude number Y coordinate of location expressed in decimal degrees in WGS84 datum. longitude number X coordinate of location expressed in decimal degrees in WGS84 datum. location:i2->organization:f1

ER 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. url string NULL ; The URL (website address) of the organization. tax_status string NULL ; Government assigned tax designation for tax-exempt organizations. tax_id string NULL ; A government issued identifier used for the purpose of tax administration. year_incorporated date 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. program program Some organizations organise their services into programs. A program brings together a number of related services. id string UNIQ; Each program must have a unique identifier. organization_id string UNIQ; Each program must belong to a single organization. The identifier of the organization should be given here. name string The name of the program alternate_name string NULL ; An alternative name for 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 services_at_location table. id string UNIQ; 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 ; Alternative or commonly used name for a service. description string NULL ; A description of the service. url string NULL ; URL of the service email string NULL ; Email address for the service status string The current status of the service. interpretation_services string NULL ; A description of any interpretation services available for accessing this service. application_process string NULL ; The steps needed to access the service. wait_time string NULL ; Time a client may expect to wait before receiving a service. fees string NULL ; 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. licenses string NULL ; An organization may have a license issued by a government entity to operate legally. A list of any such licenses can be provided here. service:i2->organization:f1 service:i3->program:f1 service_attribute service_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; Each service_attribute entry should have a unique identifier. service_id string The identifier of the service to which this taxonomy term applies. taxonomy_term_id string NULL ; The identifier of this taxonomy term from the taxonomy table. service_attribute:i2->service:f1 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; Each taxonomy term must have a unique identifier, within the scope of the dataset term 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. service_attribute:i3->taxonomy_term:f1 other_attribute other_attribute The other_attributes table creates a link between entities other than service and one or more classifications that describe the entity. id string UNIQ; Each other_attribute entry should have a unique identifier. link_id string The identifier of the entity to which this taxonomy term applies. link_type string The identifier of the entity to which this taxonomy term applies. taxonomy_term_id string NULL ; The identifier of this taxonomy term from the taxonomy table. other_attribute:i4->taxonomy_term: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; 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 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; Each location must have a unique identifier organization_id string Each location entry should be linked to a single organization. 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 The name of the location alternate_name string An alternative name for the location description string NULL ; A description of this location. transportation string A description of the access to public or private transportation to and from the location. latitude number Y coordinate of location expressed in decimal degrees in WGS84 datum. longitude number X coordinate of location expressed in decimal degrees in WGS84 datum. service_at_location:i3->location:f1 location:i2->organization:f1 phone phone The phone table contains details of the telephone numbers are used to contact organizations, services, and locations. id string UNIQ; Each entry must have a unique identifier location_id string The identifier of the location where this phone number is located service_id string The identifier of the service for which this is the phone number organization_id string The identifier of the organization for which this is the phone number contact_id string The identifier of the contact for which this is the phone number service_at_location_id string 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 The extension of the phone number type string Indicates the type of phone service, drawing from the RFC6350 list of types (text (for SMS), voice, fax, cell, video, pager, textphone). language string A comma separated list of ISO 639-1, or ISO 639-2 [language codes](available at http://www.loc.gov/s tandards/iso639-2/php/code_list.php) to represent the languages available from this phone service. The three-letter codes from ISO 639-2 provide greater accuracy when describing variants of languages, which may be relevant to particular communities. description string A description providing extra information about the phone service (e.g. any special arrangements for accessing, or details of availability at particular times. department string (Deprecated) The department for which this is the phone number. This field is deprecated and will be removed in a future version of HSDS. 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; Each contact must have a unique identifier organization_id string The identifier of the organization for which this is a contact service_id string The identifier of the service for which this is a contact service_at_location_id string The identifier of the 'service at location' table entry, when this contact is specific to a service in a particular location. name string The name of the person title string The job title of the person department string The department that the person is part of email string The email address of the person phone:i5->contact:f1 contact:i2->organization:f1 contact:i3->service:f1 contact:i4->service_at_location:f1 physical_address physical_address The addresses table contains the physical addresses for locations id string UNIQ; Each physical address must have a unique identifier. location_id string The identifier of the location for which this is the address. attention string The person or entity whose attention should be sought at the location (Often included as '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 (Deprecated) A second (additional) line of address information. (This field is deprecated: we recommend including all address information before 'city' as a comma or newline separated list in address_1. There is no guarantee that systems will read this line of address information.) address_3 string (Deprecated) A third (additional) line of address information. (This field is deprecated: we recommend including all address information before 'city' as a comma or newline separated list in address_1. There is no guarantee that systems will read this line of address information.) address_4 string (Deprecated) The fourth (additional) line of address information. (This field is deprecated: we recommend including all address information before 'city' as a comma or newline separated list in address_1. There is no guarantee that systems will read this 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). physical_address:i2->location:f1 postal_address postal_address The postal_address table contains the postal addresses for mail to a certain location. This may differ from the physical location. id string UNIQ; Each postal address must have a unique identifier location_id string The identifier of the location for which this is the postal address. attention string The person or entity whose attention should be sought at the location (Often included as '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 (Deprecated) A second (additional) line of address information. (This field is deprecated: we recommend including all address information before 'city' as a comma or newline separated list in address_1. There is no guarantee that systems will read this line of address information.) address_3 string (Deprecated) A third (additional) line of address information. (This field is deprecated: we recommend including all address information before 'city' as a comma or newline separated list in address_1. There is no guarantee that systems will read this line of address information.) address_4 string (Deprecated) The fourth (additional) line of address information. (This field is deprecated: we recommend including all address information before 'city' as a comma or newline separated list in address_1. There is no guarantee that systems will read this 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) postal_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; Each entry must have a unique identifier service_id string The identifier of the service for which this is the regular schedule location_id string The identifier of the location for which this is the regular schedule service_at_location_id string The identifier of the 'service at location' table entry, when this schedule is specific to a service in a particular location. valid_from date The date from which the schedule information is valid. valid_to date The last date on which the schedule information is valid. dtstart date iCal - The date of the first event is the schedule. Necessary when using the 'interval' feature, optional otherwise. timezone number The timezone that all dates are expressed as, expressed as a UTC offset. Dates are assumed to be UTC otherwise. until date iCal - The date of the last occurence of the recurring event. count date iCal - The number of times that the event occurs. Use this instead of 'until', if appropriate. wkst date iCal - The day on which the week starts, eg MO. freq string iCal - How often the frequency repeats. E.g. Frequency use one of WEEKLY or MONTHLY. interval number iCal - How often the frequency repeats. E.g. Interval 2 for Frequency WEEKLY gives Fortnightly byday string 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 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 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 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 Human readable description of the availability of the service. opens_at time 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 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: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; Each entry must have a unique identifier organization_id string The identifier of the organization in receipt of this funding. service_id string The identifier of the service in receipt of this funding source string A free text description of the source of funds for this organization or service. funding:i2->organization:f1 funding:i3->service:f1 eligibility eligibility The eligibility tables contains details of the eligibility criteria for particular services id string UNIQ; Each entry must have a unique identifier service_id string The identifier of the service for which this entry describes the eligibility criteria eligibility:i2->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; Each service area must have a unique identifier service_id string The identifier of the service for which this entry describes the service area service_area string 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 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. 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; Each document must have a unique identifier service_id string The identifier of the service for which this entry describes the required document document string The document required to apply for or receive the service. e.g. 'Government-issued ID', 'EU Passport' required_document:i2->service:f1 payment_accepted payment_accepted The payment_accepted table contains details of the methods of payment that can be used in order to pay for services id string UNIQ; Each entry must have a unique identifier service_id string The identifier of the services for which the entry describes the accepted payment methods payment string The methods of payment accepted for the service payment_accepted: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; Each language must have a unique identifier service_id string The identifier of the service for which the entry describes the languages in which services are delivered location_id string The identifier of the location for which the entry describes the languages in which services are delivered language string Languages, other than English, in which the service is delivered. Languages are listed as ISO639-1 codes. language:i2->service:f1 language:i3->location:f1 accessibility_for_disabilities accessibility_for_disabilities The accessibility_for_disabilities table contains details of the arrangements for access to locations for people who have disabilities id string UNIQ; Each entry must have a unique identifier location_id string The identifier of the location for which the entry describes the accessibility provision accessibility string 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. accessibility_for_disabilities: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 UNIQ; Each service, program. location, address, or contact will have a unique identifier. resource_type string UNIQ; 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 language string character_set string