Changes between Version 2 and Version 3 of REDCap sql fields

Timestamp:

01/29/14 15:00:03 (10 years ago)

Author:

Nick Holden

Comment:

--

REDCap sql fields

@@ -56 +56 @@
 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).
+project_name is easy, because you can search for it in the redcap_projects database where project_id matches (look at the URL for the project to see the "pid=XX" attribute. field_name is kind of easy, because you can search for it in the redcap_metadata table, where project_id matches and the human-readable string is in element_label.
+
+To find the form_name, you need to look for entries in the redcap_metadata where the project_id matches, the form_menu_description is not null (it will have the name of the forms in human readable format). Basically the first element in each form has a value in this column, and all other fields do not.
+
+By using foreign keys we could 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, unless you built a very complex query).
 
 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.
+
+The other approach is to create a project, with a data collection form to collect values and labels, and then query the redcap_data table for those entries. This is popular with the REDCap community but I (NH) really dislike it, as it puts metadata in the data table, which seems to be contrary to the database model of REDCap.
+
+To begin with, we will use the table definition above, and do manual lookups for the project, form, and field names.
+
+=== Re-use of option list ===
+
+Of course, sometimes you want to use the same list more than once. In this case, the form and field names in the database should relate to the question which makes FIRST use of the list.