Schema
The schema is another iteration of the initially designed schema (Schema v1) and defines a total of 5 (almost fully) normalised data tables:
- outcomes: all quantitative data points
- indicators: a table defining the principal metrics, including aggregates and individual survey questions
- insights: table for storing the key insights that each is based on a single survey question (stored in indicators)
- surveys: a basic look-up table storing all survey related information
- subjects: a basic look-up table for storing all participating agencies (when agency level data is included) or agency aggregates ("All of Government")
Note: initially and while agency-level data is not published, the subjects table will only include 1 entry for All of Government.
This diagram shows the individual fields of each table, indicating private (PK) and foreign (FK) keys, as well as the relationships between the tables
One row for every outcome value, subject and year with subjects being government agencies, all of government or potentially also sectors of agencies
| Field | Type | Foreign Key | Required | Description | Example |
|---|---|---|---|---|---|
| survey_id | string | yes | yes | reference to survey | 1 |
| subject_id | string | yes | yes | the government entity or sector | aog |
| indicator_id | string | yes | yes | reference to indicator | fa1 |
| value | string | no | yes | the outcome | 30 |
| answer | string | no | no | the answer value if indicator is a survey question - for yes/no questions one of yes, no, not_sure, not_warranted, not_stated, developing, like_to or other suitable values for other multiple choice questions (download, api) |
not_sure |
| answer_text | string | no | no | the answer display text if indicator is a survey question | Are not sure |
One row for every survey
| Field | Type | Required | Description | Example |
|---|---|---|---|---|
| survey_id | string | yes | the survey id | 1 |
| date | string | yes | the survey date | 20180801 |
| agencies_total | number | no | number of participating government agencies | 22 |
One row for every subject of interest with subjects being government agencies, all of government or potentially also sectors of agencies. Referenced from Outcomes table.
| Field | Type | Required | Description | Example |
|---|---|---|---|---|
| subject_id | string | yes | the subject id | all |
| title | string | yes | the subject title | All of Government |
One row for every outcome indicator. Implicitly referenced from Outcomes table columns or from Outcomes table row (alternative design)
| Field | Type | Required | Description | Example |
|---|---|---|---|---|
| indicator_id | string | yes | the indicator id | fa1 |
| title | string | yes | title of indicator or survey question | Data Management Processes |
| description | string | yes | description for indicator or survey question | Identifies the key business processes that underpin data ... |
| parent_indicator_id | string | no | parent-indicator, e.g the focus area indicator a question indicator belongs to | fa1 |
| type | string | no | type of indicator - one of percentage, absolute, qualitative (TBD) | percentage |
| question_no | number | no | the survey question number | 26 |
One row for every year and key insight outcome
| Field | Type | Foreign Key | Required | Description | Example |
|---|---|---|---|---|---|
| survey_id | string | yes | yes | reference to survey | 1 |
| indicator_id | string | yes | yes | insight reference | insight1 |
| value | number | no | yes | a percentage outcome for specific insight | 90 |
| text | string | no | yes | the outcome comment/text for insight | of agencies have released data |
| outcome_answers | string | yes | no | a list of answer values that the insight refers to | no, not_sure |