GData Schema Design
Back to Requirements
Back to Implementer's Guide
Overview
This document describes the various spreadsheets used in the GData Schema. One important naming convention is that, rather than using underscores, periods are used to separate words from the same column. Using underscores caused problems in querying.
Spreadsheet: water_system
Description
This spreadsheet describes valid water systems. If a user attempts to record information for a water system that is not present here, an error is displayed to the SMS sender.
Columns
- id
- The water system's shorthand id.
- name
- The water system's name.
- plant.capacity
- A piece of auxiliary information about the water system.
- raw.tds
- A piece of auxiliary information about the water system.
- A column is also included in this spreadsheet for each entry in the data_type spreadsheet, specifying target values for each data type. For example, the column "i" in this spreadsheet corresponds to the "RO Inlet Pressure", which has shorthand encoding "i", and is used in this spreadsheet to specify a target value for that data type for a given water system. The use of target values is entirely optional, and if no target value is specified, or even if an entire column for a data type is not present, it is ignored.
Spreadsheet: authorized_phone
Description
This spreadsheet describes cell phone numbers from which SMS messages may be received. If an SMS is received from a cell phone with an unrecognized number, an error is presented to the user.
Columns
- name
- The name of the cell phone holder. (Not actually used by the system, only for internal uses)
- phone.number
- The cell phone number, with no spaces, parentheses, or dashes.
Spreadsheet: data_type
Description
This spreadsheet describes all data types for which it is valid to store information.
Columns
- name
- A name description of this data type.
- encoding
- A short-hand encoding of the data type. This value is used in other spreadsheets to reference a data type, and is also used by SMS senders to specify a data type for which to record information.
- type
- The underlying type of the data type. Currently, "integer", "boolean", and "string" are supported. Boolean values are represented by "Y":"N" in an SMS message. If a user accidentally specifies a parameter for a data type that does not match its underlying type, an error is displayed to the user.
- max.length
- The maximum length of a valid value for this data type.
- units
- The units used to measure this data type.
- deviation.limit.percentage
- The maximum percentage (represented as a value from 0 to 100, floating point numbers are ok) that value for this data type can deviate from its corresponding target value in the water_system spreadsheet and still not be flagged as deviated in the reports.
Spreadsheet: water_system_data
Description
A spreadsheet containing information about all data types for a given water system on a given day.
Columns
- date
- The date that the information was recorded for the water system. This must be set as a "plain text" formatted column in the Google spreadsheet, otherwise problems can occur with comparisons because Google will try to format it differently.
- phone.number
- The phone number from which the last update to the given row came from.
- water.system
- The id of the water system for which this row describes. (Corresponds to id column in water_system)
- What follows is a column for each data type. The name of the column is the data type’s short hand encoding above.
Spreadsheet: login
Description
This spreadsheet describes all of the username/password pairs that allow access to the Management portion of the application for viewing reports.
Columns
- username
- A username.
- password
- The corresponding password.
