250 likes | 469 Views
The Study Database. Robert R. Kelley, Ph.D. The Study Database. Outline. Overview Designing the Database Building the Database. The Study Database. Over. The Study Database: Tool(s) used to store data electronically for queries and data analysis. Components of the Study Database.
E N D
The Study Database Robert R. Kelley, Ph.D.
The Study Database Outline • Overview • Designing the Database • Building the Database
The Study Database Over • The Study Database: • Tool(s) used to store data electronically for queries and data analysis
Components of the Study Database The Study Database Case Report Form Study Database Query and Monitoring Software Analysis Software
The Study Database Designing the Study Database
The Study Database The Case Report Form Should be driven by the study protocol Good database design starts with a good Case Report Form Should support the study statistical plan Should be divided into logical sections or multiple forms
The Study Database case id Carefully consider variable names Use only lower case procedure type physician Be consistent!!! Case ID Procedure Type Use underscore to separate words procedure_type case_id Temperature Physician dbp Abbreviate when possible Diastolic BP temp Systolic BP Comments Consider prefixing exam_temp Use the shortest variable name you can!!!
The Study Database Coding levels For binary data always code with 1/0 1 = Male, 0=Female Case ID Sex For Yes/No, always code with 1/0 1=Yes, 0,No Diabetes 1, Beta-lactam monotherapy only 2, Beta-lactam + macrolide combination only 3, Beta-lactam + quinolone combination only 4, Quinolone monotherapy only Congestive Heart Failure For multiple choice data, code 1. . .N Antibiotic Don’t forget to code for NA/UNKNOWN 1 = Yes, 0=No -1 UK Diagnosis Comments Use alpha numeric codes when they make sense blmono, Beta-lactam monotherapy only blmac, Beta-lactam + macrolide combination only blq, Beta-lactam + quinolone combination only quin, Quinolone monotherapy only
The Study Database The Data Dictionary Variable name Can be a simple spreadsheet with . . . Data type Units Coding levels Description Coding restrictions
The Study Database An example. . . Can be as detailed as necessary This will often be database dependent Very important for consistency Derived from protocol and study definitions Very important – especially for lab values Defined according to rules
The Study Database Building the Study Database
The Study Database Types of Studies from a Data Perspective > 100 cases > 20 Variables <100 cases <20 Variables Large Studies Small Studies
The Study Database Tools for Smaller Studies MS Access Spreadsheet Applications MS Excel Personal Database Applications OpenOffice-Base OpenOffice-Calc Apple - Numbers Filemaker Pro
The Study Database Excel Strengths Limitations Many people in science and medicine are already familiar with Excel No capability for metadata Easy to back up and have multiple copies for safekeeping. Does not provide authentication, authorization, audit trail MS Excel Supports descriptive statistics and calculations Inadvertent data corruption Depending on verison, may be limited to 256 columns (variables) Provides rudimentary data visualization Need to encrypt manually
The Study Database Access Strengths Limitations Provides a front-end for easy form creation Must understand the basics of database design to be able build effective databases Easy to back up and have multiple copies for safekeeping. Does not provide authentication, authorization, audit trail MS Access Supports descriptive statistics and calculations Programming help may be required for complicated features Provides rudimentary data visualization Need to encrypt manually
The Study Database Tools for Larger Studies OpenClinica LabKey Database / Applications Medidata Rave REDCap Custom Software
The Study Database Unlike Excel and Access these tools require infrastructure and specialized skills to maintain.
The Study Database Tools for Larger Studies Place to “host” your database Licenses for software (much more expensive than Excel/Access) Recommendation Hire a dedicated person to administer this system or contract it out. Infrastructure Technical Skills to install and maintain system
The Study Database OpenClinica https://www.openclinica.com • Designed for capturing data for clinical trials • Has paid and free versions • Can be hosted locally or by Akaza Research LLC • Supports exporting data to: • HTML • Tab-Delimited • SPSS Syntax and Data • CDISC ODM xml 1.3 and 1.2 • CDISC OpenClinicaExentsion 1.3 and 1.2 • Specifications • Windows Server 2008 or Linux Variant • Java • Java Application Server (Apache) • Database: PostgreSQL 8.4 or Oracle 10.2g
The Study Database LabKey https://www.labkey.org/ • Designed for storing lab data such as: • Specimen data • Gene Sequences • Flow Cytometry Data • Proteomics Data • Any Assay Data • It also supports storing other clinical data • Has paid and free versions • Can be hosted locally or by Akaza Research LLC • Supports exporting data to: • Excel • Text • Javascript • R • SAS • Specifications • Windows Server 2008 or Linux Variant • Java • Java Application Server (Apache) • Database: PostgreSQL 8.4 or Microsoft SQL Server
The Study Database Medidata Rave http://www.mdsol.com/products/rave_overview.htm • A comprehensive commercial clinical data management system • Very flexible • Expensive and requires significant technical support • Supports exporting data to: • SAS • CSV • XML • Specifications • Windows Server 2008 or Linux Variant • Java • Java Application Server (Apache) • Database: PostgreSQL 8.4 or Microsoft SQL Server
The Study Database REDCap • A comprehensive open source clinical data management system • Supports creating: • Surveys • Data Entry Forms (eCRFs) • Longitudinal Studies • Free, but requires significant technical support • Supports exporting data to: • Excel • SPSS • SAS • R • STATA http://project-redcap.org/ • Specifications • Windows Server 2008 or Linux Variant • PHP • Database: MySQL Server • SMTP email server
The Study Database Custom Software If your requirements are extremely particular you may want to build your own system Can be time consuming and resource intensive NOT RECOMMENDED
References • Beyond CRES: Excel - Informatics tools for managing your clinical research data, Anderson N. www.iths.org/bmi • Why not use Excel for data management?, Carlin L., Bondy J, Wolfe P. http://connect.ucdenver.edu/excel/