Turtle Sense/Database design - Revision history

Sam: /* Table: NESTS */ ids

2014-11-23T01:37:59Z

Table: NESTS: ids

Sam: /* Table: NESTS */ crabs

2014-09-13T03:46:15Z

Table: NESTS: crabs

Sam: /* Table: NEST_ALERTS */ user rights

2014-08-18T19:30:08Z

Table: NEST_ALERTS: user rights

Sam: /* Table: EVENTS */ DD->D

2014-07-18T19:17:19Z

Table: EVENTS: DD->D

Sam: /* Table: REPORTS */ clarify

2014-07-17T08:03:27Z

Table: REPORTS: clarify

Sam: /* Table: REPORTS */

2014-07-10T02:59:46Z

Table: REPORTS

Sam: /* Table: REPORTS */ Average Magnitude

2014-07-10T02:50:05Z

Table: REPORTS: Average Magnitude

Sam: Max_jerk

2014-07-10T02:43:41Z

Max_jerk

Sam: /* Table: RECORDS */ x, y and z converted from hex

2014-07-10T02:09:59Z

Table: RECORDS: x, y and z converted from hex

Sam: /* Table: ORGANIZATIONS */ two more fields

2014-07-09T19:27:13Z

Table: ORGANIZATIONS: two more fields

@@ Line 40: / Line 40: @@
 ===Table: NESTS===
 Each unique nest is identified by the date a sensor was installed, and the serial number of the sensor.  The registration device stores this information in the sensor, and every report sent from that sensor will start with the unique identifier
-*Nest_ID: a unique sequential number to index to the nest
+*Nest_ID: a unique sequential number to index to the nest (This is the database ID and not the organization's ID)
 *Long_nest_ID: (eg. 2014-06-20_AA0014) Also the beginning of the file name of reports.  This uniquely identifies a nest
-*Org_ID: The way the organization IDs each nest.
+*Org_ID: The ID of the organization managing the nest.
 *Reg_date_time: The date is also the first term in the file name  (see the Long_nest_ID field above)
 *Days_when_reg: (0) The number of days after the eggs were laid before registration (normally zero -- manually adjusted for reporting and prediction if later)

@@ Line 52: / Line 52: @@
 *Active: Yes or No -- whether the nest is still being monitored
 *Current_Parameters_ID: The id of the parameters currently in use
-*Activity_level: the current level of activity in the nest (this will be calculated)
+*Turtle_Activity_level: the current level of turtle activity in the nest (this will be calculated)
 *Boil_date_predicted: predicted date and time of hatching
 *Boil_date_actual: observed date and time of hatching

@@ Line 233: / Line 233: @@
 ===Table: NEST_ALERTS===
 *Org_ID
-*All_nests: (Yes or No) whether all nests for this organization will generate an alert
+*All_nests: (Yes or No) whether all nests for this organization will generate an alert ''(U,D,A,B)''
 *Nest_ID: If All_nests is no, the specific nest that is being monitored
 *User_ID:  The person monitoring the nest
-*All_updates: (Yes or No)  whether alerts are sent regularly updating the status of the nest for all events
+*All_updates: (Yes or No)  whether alerts are sent regularly updating the status of the nest for all events ''(D,A,B)''
-*Hatching: (Yes or No)  whether alerts are sent only when hatching is imminent
+*Hatching: (Yes or No)  whether alerts are sent only when hatching is imminent ''(U,D,A,B)''
-*Problems: (Yes or No)  whether alerts are sent when nests are disturbed, sensors are disconnected, power is reset or low batteries cause a shutdown.
+*Problems: (Yes or No)  whether alerts are sent when nests are disturbed, sensors are disconnected, power is reset or low batteries cause a shutdown. ''(A,B''
-*Parameters: (Yes or No)  whether alerts are sent when parameters are changed
+*Parameters: (Yes or No)  whether alerts are sent when parameters are changed ''(A,B)''
-*High_motion: (Yes or No)  whether alerts are sent when motion above a threshold is detected
+*High_motion: (Yes or No)  whether alerts are sent when motion above a threshold is detected ''(D,A,B)''
-*Low_motion: (Yes or No)  whether alerts are sent when motion below a threshold is detected
+*Low_motion: (Yes or No)  whether alerts are sent when motion below a threshold is detected ''(D,A,B)''
-*High_temp: (Yes or No)  whether alerts are sent when temperature above a threshold is detected
+*High_temp: (Yes or No)  whether alerts are sent when temperature above a threshold is detected ''(D,A,B)''
-*Low_temp: (Yes or No)  whether alerts are sent when temperature below a threshold is detected
+*Low_temp: (Yes or No)  whether alerts are sent when temperature below a threshold is detected ''(D,A,B)''
-*Motion_high_thresh:  The high motion threshold for triggering an alert
+*Motion_high_thresh:  The high motion threshold for triggering an alert ''(D,A,B)''
-*Motion_high_thresh:  The low motion threshold for triggering an alert
+*Motion_high_thresh:  The low motion threshold for triggering an alert ''(D,A,B)''
-*Temp_high_thresh:  The high temperature threshold for triggering an alert
+*Temp_high_thresh:  The high temperature threshold for triggering an alert ''(D,A,B)''
-*Temp_low_thresh:  The high temperature threshold for triggering an alert
+*Temp_low_thresh:  The high temperature threshold for triggering an alert ''(D,A,B)''
-*Hatching_alert_format: (E - Email, S - SMS, B - Both)
+*Hatching_alert_format: (E - Email, S - SMS, B - Both) ''(U, D,A,B)''
-*Problem_alert_format: (E - Email, S - SMS, B - Both)
+*Problem_alert_format: (E - Email, S - SMS, B - Both) ''(A,B)''
-*Param_alert_format: (E - Email, S - SMS, B - Both)
+*Param_alert_format: (E - Email, S - SMS, B - Both) ''(A,B)''
-*Thresh_alert_format: (E - Email, S - SMS, B - Both) for all alerts based on thresholds
+*Thresh_alert_format: (E - Email, S - SMS, B - Both) for all alerts based on thresholds ''(D,A,B)''
 ==Data parsing==

@@ Line 11: / Line 11: @@
 *Nest_ID: sequentially numbered key index of NESTS
 *Report_ID: sequentially numbered key index of REPORTS
-*Sensor IDD: sequentially numbered key index of SENSORS
+*Sensor ID: sequentially numbered key index of SENSORS
 *Comm_ID: the comm unit that created the report
 *Reset (Yes or No)

@@ Line 22: / Line 22: @@
 *Report_ID: sequentially numbered key index
 *Event_ ID: a sequential numbered key index of EVENTS
-*File_name (eg. 2014-06-20_AA0014_r-005-03) can be used to determine nest record.  Starts with date, and then the sensor
+*File_name (eg. 2014-06-20_AA0014_r-005-03) can be used to determine nest record.  Starts with registration date, and then the sensor
 *Days_active: calendar days since the sensor was recorded.  001 is the day of recording, 002 is the next day, etc...
 *Report_number: the number of the report issued for this nest, on this day

@@ Line 29: / Line 29: @@
 *Number_of_records: Converted from Hex, number of records as reported
 *Average_magnitude: The average of the Magnitude field of all the RECORDS in this report
 *Parameters_ID: The parameters used to generate the data -- will be set on-line in the future

@@ Line 104: / Line 104: @@
 *Bin_25
 *Missing: This is the number of bins that are below the lowest bin reported, when Maximum is greater than 0A
-*Energy: This is calculated by integrating all the readings
+*Energy: The average energy level. This is calculated by integrating all the readings and then dividing by the number of readings
 ===Table: PARAMETERS===
@@ Line 386: / Line 387: @@
 *Missing (3838) This would be the number of bins that are below the lowest bin reported, when Maximum is greater than 0A
 *Energy   This can be computed by integrating all the readings. Each bin value is multiplied by the bin energy level and then added to the energy value.  The bin energy values are adjusted by the average magnitude for an entire report which should correspond to one G.

@@ Line 61: / Line 61: @@
 *Temperature: Hex reading converted (converted to decimal and then divided by 25.6)
 These x,y and z hex readings are the static readings of the motion sensor. They are the vector of force acting on the meter, so should average 1G
-*X_hex
+*X: static x reading converted from hex
-*Y_hex
+*Y: static y reading converted from hex
-*Z_hex
+*Z: static z reading converted from hex
-*Magnitude:  This is the x, y and z hex readings converted to a magnitude (x^2 + y^2 +z^2)^1/2
+*Magnitude:  This is the x, y and z readings converted to a magnitude (x^2 + y^2 +z^2)^1/2
 *Readings:The total number of sensor readings taken in this record
 *Maximum: The highest bin reading in the record

@@ Line 193: / Line 193: @@
 ===Table: ORGANIZATIONS===
 *Org_ID: Unique key field for each organization
 *Name: Full name of the organization
 *Date_active: Date organization began collecting data