Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
61 lines (49 sloc) 4.22 KB

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 organisations, 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 |
+================================+==============+=========+==========+=====================+
| programs                       | X            |         |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| funding                        | X            | X       |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| service_taxonomy               |              | X       |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| phone*                         | X            | X       | X        | X                   |
+--------------------------------+--------------+---------+----------+---------------------+
| contact                        | X            | X       |          | X                   |
+--------------------------------+--------------+---------+----------+---------------------+
| regular_schedule               |              | X       | X        | X                   |
+--------------------------------+--------------+---------+----------+---------------------+
| holiday_schedule               |              | X       | X        | X                   |
+--------------------------------+--------------+---------+----------+---------------------+
| eligibility                    |              | X       |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| service_area                   |              | X       |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| required_document              |              | X       |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| payment_accepted               |              | X       |          |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| language                       |              | X       | X        |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| physical_address               |              |         | X        |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| postal_address                 |              |         | X        |                     |
+--------------------------------+--------------+---------+----------+---------------------+
| accessibility_for_disabilities |              |         | X        |                     |
+--------------------------------+--------------+---------+----------+---------------------+

*Phone can also be linked to contact.

When a single row contains multiple foreign keys, these will be interpreted as 'OR' relationships.

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

ER Diagram for HSDS v 1.1

ER Diagram HSDS v1.1