CamCOPS
CamCOPS – Summary tables
Back to Documentation
www.camcops.org/documentation/summary_tables.html

The summary tables offered by CamCOPS

CamCOPS stores its data in tables and offers views to select current data (as opposed, say, to deleted data).

However, CamCOPS seeks to avoid storing duplicated data. For example, if a questionnaire has 10 items, each scored 0–5, then each will be stored as an integer field in the database. What about the total? That is typically not stored — because that would bring the possibility that an edit left a “total” field out of sync with its component parts.

Normally, you would view totals through the web viewer, which will generate them on the fly. Occasionally, you might need totals to be written to the database, e.g. for further data exporting. For this, there is a summary table feature.

Creating the summary tables

What’s in the summary tables

For example, the PHQ-9 task offers the following pieces of summary information:

is_complete (BOOLEAN)
total (INTEGER)
n_core (INTEGER)
n_other (INTEGER)
n_total (INTEGER)
is_mds (BOOLEAN)
is_ods (BOOLEAN)
severity (TEXT)

... and so it will create a table named phq9_SUMMARY_TEMP, with these fields for each task instance, plus a foreign key to its source table, and the creation date/time of the source record (as a DATETIME, to save you parsing the text-based date/time format used in the main record’s when_created field), and the creation date/time of the summary:

phq9_pk (INTEGER)
when_source_record_created_utc (DATETIME)
when_summary_created_utc (DATETIME)

To work out which records are current, you could link to the main table (in this case, phq9), or use the helpful views that are automatically created.

Here’s some example output:

mysql> select * from phq9_SUMMARY_TEMP;
+---------+--------------------------------+--------------------------+-------------+-------+--------+---------+---------+--------+--------+-------------------+
| phq9_pk | when_source_record_created_utc | when_summary_created_utc | is_complete | total | n_core | n_other | n_total | is_mds | is_ods | severity          |
+---------+--------------------------------+--------------------------+-------------+-------+--------+---------+---------+--------+--------+-------------------+
|       1 | 2013-04-22 18:26:22            | 2013-07-23 11:46:44      |           1 |    10 |      0 |       3 |       3 |      0 |      0 | moderate          |
|       2 | 2013-04-28 22:26:23            | 2013-07-23 11:46:44      |           1 |    27 |      2 |       7 |       9 |      1 |      0 | severe            |
|       3 | 2013-04-28 22:26:43            | 2013-07-23 11:46:44      |           0 |     2 |      1 |       0 |       1 |      0 |      0 | none              |
|       4 | 2013-04-29 23:26:03            | 2013-07-23 11:46:44      |           1 |    18 |      2 |       4 |       6 |      1 |      0 | moderately severe |
|       5 | 2013-05-03 17:09:53            | 2013-07-23 11:46:44      |           1 |    18 |      2 |       7 |       9 |      1 |      0 | moderately severe |
|       6 | 2013-05-03 17:10:21            | 2013-07-23 11:46:44      |           1 |     0 |      0 |       0 |       0 |      0 |      0 | none              |
|       7 | 2013-05-19 21:35:39            | 2013-07-23 11:46:44      |           1 |    12 |      0 |       4 |       4 |      0 |      0 | moderate          |
|       8 | 2013-05-03 17:09:53            | 2013-07-23 11:46:44      |           1 |    18 |      2 |       7 |       9 |      1 |      0 | moderately severe |
|       9 | 2013-05-03 17:10:21            | 2013-07-23 11:46:44      |           1 |     0 |      0 |       0 |       0 |      0 |      0 | none              |
|      10 | 2013-05-19 21:35:39            | 2013-07-23 11:46:44      |           1 |    12 |      0 |       4 |       4 |      0 |      0 | moderate          |
|      11 | 2013-05-28 22:49:17            | 2013-07-23 11:46:44      |           0 |     0 |      0 |       0 |       0 |      0 |      0 | none              |
+---------+--------------------------------+--------------------------+-------------+-------+--------+---------+---------+--------+--------+-------------------+
11 rows in set (0.00 sec)

Try this, too:

select * from phq9_SUMMARY_TEMP_current_withpt;
Valid HTML 4.01 Transitional
Valid CSS