Changes between Version 1 and Version 2 of REDCap sql fields

Timestamp:

01/28/14 18:13:04 (11 years ago)

Author:

Nick Holden

Comment:

--

REDCap sql fields

@@ -16 +16 @@
 
 {{{
-select record, value from redcap_data where project_id = 390 and field_name = 'institution' and record in (select distinct record from redcap_data where project_id = 390 and field_name = 'consortium_status' and value != '4') order by value 
+select record, value from redcap_data where project_id = 390 and field_name = 'institution' and record in (select distinct record from redcap_data where 
+project_id = 390 and field_name = 'consortium_status' and value != '4') order by value 
 }}}
 
@@ -31 +32 @@
 For those SQL fields created in LCBRU REDCap implementations which are not populated from other REDCap-derived data (i.e. not referencing the redcap_data table), the question arises of where to store the source data.
 
-To avoid proliferation of tables within the LCBRU implementations of REDCap, it is proposed that a single additional table be created for all stored source data for sql fields. The table created will need to have at a minimum four columns: an object ID, a key to indicate which data collection instrument and which question the object relates to, the value and the label. Only the last two columns can be returned to the REDCap sql query.
+To avoid proliferation of tables within the LCBRU implementations of REDCap, it is proposed that a single additional table be created for all stored source data for sql fields. The table created will need to have at a minimum four columns: an object ID, a key to indicate which data collection form and which question the object relates to, the value and the label. Only the last two columns can be returned to the REDCap sql query.
 
+Might be better to have separate columns for study, form and question (field, in REDCap parlance).
+
+'''DRAFT database table definition:'''
+
+{{{
+CREATE TABLE sql_fields (
+id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+project VARCHAR(100),
+form VARCHAR(100),
+field VARCHAR(100),
+value VARCHAR(100),
+label VARCHAR(100),
+INDEX 'reference' ('project','form','field'),
+KEY 'value_key' ('project','form','field','value'),
+KEY 'label_key' ('project','form','field','label'),
+FOREIGN KEY ('project') REFERENCES redcap_projects (project_name)
+  ON UPDATE CASCADE,
+);
+}}}
+
+Entries into the table should therefore use the project_name from the REDCap redcap_projects database table (usually an all-lower-case rendition of the project title with underscores for spaces) for 'study', form_name and field_name from the redcap_metadata table for 'form' and 'field' respectively.
+
+By using foreign keys we can force adherence to the REDCap object model and maintain the references even if the source data changes (although the SQL query itself would need to be updated in this situation).
+
+But I think you can only use a foreign key if the corresponding column holds unique values, and that isn't true for the form name or field name columns in redcap_metadata - fields and forms only have to be unique within a particular project, not across the whole database. So maybe we can't have foreign keys for all three.
+
+Need to check with the REDCap data model. I've asked for more information. In the meantime the above would work, but in a limited way, with no updating for field or form names.