Skip to content

Latest commit

 

History

History
478 lines (430 loc) · 27.7 KB

openreferral.md

File metadata and controls

478 lines (430 loc) · 27.7 KB
Raw

OpenReferral

This specification defines a minimum set of data elements for OpenReferral, a human services data interchange specification. The idea behind the specification is to create shareable multi-purpose human service data sets. The goal is to derive a specification robust enough to support Information and Retrieval (IR) applications, yet flexible enough to support localized and specialized directories. The focus of this specification is content but a significant portion of the specification is dedicated to a design that reduces data structures into comma delimited files (CSV).

1.0 Term Definitions

  • Field required - The field column must be included in your feed, and a value must be provided for each record. Some required fields permit an empty string as a value. To enter an empty string, just omit any text between the commas for that field. Note that 0 is interpreted as "a string of value 0", and is not an empty string. Please see the field definition for details.

  • Field optional - The field column may be omitted from your feed. If you choose to include an optional column, each record in your feed must have a value for that column. You may include an empty string as a value for records that do not have values for the column. Some optional fields permit an empty string as a value. To enter an empty string, just omit any text between the commas for that field. Note that 0 is interpreted as "a string of value 0", and is not an empty string.

2.0 Conventions

This specification uses snake case where compound words are joined by an underscore. Field names are all lower case and in snake case, e.g. organization_id.

3.0 Data Model

The basic entities are an organization or agency, a location (or site), and a service or program. The structure supports a model where organization has one or more locations that provide one or more services, or conversely one or more services can have one or more locations.

The lowest common denominator for data exchange is the comma delimited text file or CSV. CSV are simple table structures. In order to represent complex data types such as arrays and objects, they need to be decomposed into individual tables that are joined to parent tables via a foreign key. The entity relationship diagram below shows how the CSV files are joined to form a single organization.

"Entity-relationship"

4.0 Content

The data model is divided in organizations, locations and services. The following tables define the minimum set of data needed for a human services data specification.

4.1 Tables

Organization

Name Type Requirement Detail
organization_id int required ID that uniquely identifies the organization
name string required Name of the organization
fein string optional Federal Employer Identification Number
aka string optional Alias or Also Known As
legal_status string optional Legal business structure of the organization
year_of_incorporation date optional Date organization was founded or incorporated
licenses text optional Licenses that the organization holds
accreditation text optional Accreditations earned by the organizations

Location

Name Type Requirement Detail
location_id int required ID that uniquely identifies the location
organization_id int required ID that uniquely identifies the organization the location belongs to
name string required Name of the location
aka string optional Alias or Also Known As
accessibility array of strings optional Accessibility options available at the location
coordinates text optional Coordinates of the location expressed as [longitude, latitude] using the WGS84 datum
description string required Description of services provided at the location
location_hours string optional Hours of operation for the location
location_languages array of strings optional Languages spoken at the location
name string required Name of the location
phones array of objects optional Phone numbers for the location. See Phones section for object fields.
short_desc string required Succinct description of services provided at the location. 200 characters maximum.
transportation string optional Public transportation options near the location
accessibility enumerated or comma separated strings optional Accessibility features at the location

Service

Name Type Requirement Detail
location_id string required An ID that uniquely identifies the location the service belongs to
organization_id int required ID that uniquely identifies the organization the location belongs to
name string optional Name of the service
service_id string required An ID that uniquely identifies the service
audience string optional Group of people served
description string optional Description of the service provided
eligibility string optional Requirements to receive the service
fees string optional Fees charged to receive the service
keywords text optional Keywords that define this service for search purposes
how_to_apply string optional Description of how to apply to receive the service
service_areas text optional Cities, counties and areas served
short_desc string optional Succinct description of the service provided. 200 characters maximum.
service_hours string optional Hours of operation for the service
service_languages comma-separated strings optional Languages in which service is regularly available
wait string optional Wait times associated with the service
method_of_payment comma separated strings optional Types of payment accepted, can be enumerated

Address

HSD Name Data Type Required Detail
location_id string required ID that uniquely identifies the location the address belongs to
organization_id int required ID that uniquely identifies the organization the location belongs to
type string required Denotes whether it is a physical or mailing address
attention string optional Name of person or organization receiving mail
street string required Contains the street number, street prefix, street name, street postfix
city string required The city or appropriate administrative unit where the location resides
state string required The state or appropriate administrative unit where the location resides, If in the US, use the two letter abbreviation
postal_code string required The postal code where the location resides.
country string optional The country where the location resides. Use ISO 3166 for country designators.

Contact

Name Type Requirement Detail
location_id int required ID that uniquely identifies the location
organization_id int required ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
type string required Differentiate between service and administrative contacts
contact_name string required The Contact's full name
title string required The Contact's title
email string optional The Contact's email address
fax string optional The Contact's 10-digit US fax number. Formatting is lenient.
phone string optional The Contact's 10-digit US phone number. Formatting is lenient.
extension string optional The Contact's phone number extension. For example: "x1234"

Phone

Name Type Requirement Detail
location_id string required ID that uniquely identifies the location the phone number belongs to
organization_id int required ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
phone_number string required The Location's 10-digit US phone number. Formatting is lenient.
vanity_number string optional The Location's 10-digit US phone number with vanity letters. For example: 703-555-HELP
phone_extension string optional The Location's phone number extension. For example: "x1234"
phone_hours string optional Hours that phone number is manned
department string optional The Location's department this phone number reaches.
type string required Phone type values can be voice, fax, or tty.
country_code string optional Country telephone code prefix based in ITU E.123 standard.

Internet_Resource

Name Type Requirement Detail
location_id string optional ID that uniquely identifies the location the phone number belongs to
organization_id int optional ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
resource text required URI or email address to resource
resource_type required Type of resource: email, website, application

Funding

Name Type Requirement Detail
organization_id int optional ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
source text required URI or email address to resource

5.0 Data Format

The OpenReferral interchange format is composed of a set of files. This section defines the format and contents of files.

  • Data is saved in a comma-delimited file, string elements must be enclosed by double quotes
  • The first line of each file must contain field names. Each subsection of the Field Definitions section corresponds to one of the files and lists the field names you may use in that file.
  • Field names are case sensitive
  • Field values may not contain tabs, carriage returns or new lines *Field values that contain quotation marks or commas must be enclosed within quotation marks. In addition, each quotation mark in the field value must be preceded with a quotation mark. This is consistent with the manner in which Microsoft Excel outputs comma-delimited (CSV) files. For more information on the CSV file format, see http://tools.ietf.org/html/rfc4180.
    • The following example demonstrates how a field value would appear in a comma-delimited file:
    • Original field value: Contains "quotes", commas and text
    • Field value in CSV file: "Contains ""quotes"", commas and text"
  • Field values must not contain HTML tags, comments or escape sequences.
  • Remove any extra spaces between fields or field names.
  • Each line must end with a CRLF or LF line break character.
  • Files should be encoded in UTF-8 to support all Unicode characters.

5.1 Files

The following table list the files that make up the OpenReferral interchange format.

File Required Defines
organization.csv required Organization that provides human services.
location.csv required Locations that provide human services.
services.csv required Services provided at a location.
address.csv required location addresses
contact.csv optional Location points of contact and their contact details.
phone.csv optional Phone numbers, departments, and types (voice, fax, tty).
internet_resource.csv optional Emails, websites, applications.
funding.csv optional Funding sources.

5.2 Field Definitions

Mapping JSON objects to a flat file format requires normalization of data. This means breaking up objects and arrays into individual tables and adding keys to enable joining them in either a JSON object or in a relational database.

Organization (organization.csv)

Name Type Requirement Detail
organization_id int required ID that uniquely identifies the organization
name string required Name of the organization
fein string optional Federal Employer Identification Number
aka string optional Alias or Also Known As
legal_status string optional Legal business structure of the organization
year_of_incorporation date optional Date organization was founded or incorporated
licenses comma-separated strings optional Licenses that the organization holds
accreditation comma-separated strings optional Accreditations earned by the organization

Location (location.csv)

Name Type Requirement Detail
location_id int required ID that uniquely identifies the location
organization_id int required ID that uniquely identifies the organization the location belongs to
name string required Name of the location
aka string optional Alias or Also Known As
accessibility array of strings optional Accessibility options available at the location
coordinates text optional Coordinates of the location expressed as [longitude, latitude] using the WGS84 datum
description string required Description of services provided at the location
location_hours string optional Hours of operation for the location
location_languages array of strings optional Languages spoken at the location
name string required Name of the location
phones array of objects optional Phone numbers for the location
short_desc string required Succinct description of services provided at the location. 200 characters maximum.
transportation string optional Public transportation options near the location
accessibility enumerated or comma separated strings optional Accessibility features at the location

Service (service.csv)

Name Type Requirement Detail
location_id string required An ID that uniquely identifies the location the service belongs to
organization_id int required ID that uniquely identifies the organization the location belongs to
name string optional Name of the service
service_id string required An ID that uniquely identifies the service
audience string optional Group of people served
description string optional Description of the service provided
eligibility string optional Requirements to receive the service
fees string optional Fees charged to receive the service
keywords text optional Keywords that define this service for search purposes
how_to_apply string optional Description of how to apply to receive the service
service_areas text optional Cities and Counties served.
short_desc string optional Succinct description of the service provided. 200 characters maximum.
wait string optional Wait times associated with the service
service_hours string optional Hours of operation for the service
service_languages comma-separated strings optional Languages in which service is regularly available
method_of_payment comma separated strings optional Types of payment accepted, can be enumerated

Address (address.csv)

HSD Name Data Type Required Detail
location_id string required ID that uniquely identifies the location the address belongs to
organization_id int required ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
type string required Denotes whether it is a physical or mailing address
attention string optional Name of person or organization receiving mail
street string required Contains the street number, street prefix, street name, street postfix
city string required The city or appropriate administrative unit where the location resides
state string required The state or appropriate administrative unit where the location resides, If in the US, use the two letter abbreviation
postal_code string required The postal code where the location resides.
country string optional The country where the location resides. Use ISO 3166 for country designators.

Contact (contact.csv)

Name Type Requirement Detail
location_id int required ID that uniquely identifies the location
organization_id int required ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
type string required Differentiate between service and administrative contacts
contact_name string required The Contact's full name
title string required The Contact's title
email string optional The Contact's email address
fax string optional The Contact's 10-digit US fax number. Formatting is lenient.
phone string optional The Contact's 10-digit US phone number. Formatting is lenient.
extension string optional The Contact's phone number extension. For example: "x1234"

###Phone (phone.csv)

Name Type Requirement Detail
location_id string required ID that uniquely identifies the location the phone number belongs to
organization_id int required ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
number string required The Location's 10-digit US phone number. Formatting is lenient.
vanity_number string optional The Location's 10-digit US phone number with vanity letters. For example: 703-555-HELP
extension string optional The Location's phone number extension. For example: "x1234"
department string optional The Location's department this phone number reaches.
type string required Phone type values can be voice, fax, or tty.
country_code string optional Country telephone code prefix based in ITU E.123 standard.

###Internet Resource (internet_resource.csv)

Name Type Requirement Detail
location_id string optional ID that uniquely identifies the location the phone number belongs to
organization_id int optional ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
resource text required URI or email address to resource
resource_type required Type of resource: email, website, application

###Funding (funding.csv)

Name Type Requirement Detail
organization_id int optional ID that uniquely identifies the organization the location belongs to
service_id string required An ID that uniquely identifies the service
source text required URI or email address to resource

6.0 Extending OpenReferral

The specification can be extended or modified with additional data elements. If the data type is simple it can be directly added to the organizations, locations and services table definitions. If the data type is complex, e.g. an array or an object, then an additional CSV file will be necessary. Foreign key should be assigned as needed with the intended purpose of the data.