Transcript Clinical Database Design

Access Database Design for
Clinical Research Studies
October 17, 2003
Paul A. Harris
Julie McFarlane
GCRC Research Skills Workshop
Clinical Database Design


Task #1 – Determine the discrete
measurement types and the times of
occurrence (1 hour + ?)
Excel is a great tool to perform this 1st cut
view of the data as your final product
should be a grid AND if possible, the vertical
axis can be used later to actually create the
Access tables.
Time
Discrete
Msmts
Note units and examples
given whenever possible
Drugs are
Difficult to
Capture (Dose/
Type/Start/Stop)
Same Measurement Frequency
1 Time Per Study or Patient
In a one-time study, we can group these with
the Demographic Information to reduce tables
(ie one-to-one table relationships aren’t that helpful.
Clinical Database Design
Task #2 – Ask Questions
1) Who/what/where – This will help you think of where the database
file should be stored, security, interface, etc. (JM – 1 user; JM
office – network server)

Who will enter data?, Where will data be entered?, Will more than
one person need to add data?, Where will data be stored?, Who
will need to access the data and when? (JM entering data / NB
access periodic – cannot see server)
2) What sorts of questions will you try to answer with the database –
this will help in designing tables and queries (Does ACE Inhibitor
correlate with decrease in AFIB? Initially blinded – pharmacy to
give key later by vial label number)

Will we need real-time query tools? Will we need the final
numbers packaged in a certain way? (Individual measurement
groups and/or individual measurements – all to Excel file)
Clinical Database Design
Task #3 – Table Structure
Use information gained in the Excel exercise and in the
question and answer session to determine the optimum
table design structure.
Work on paper before committing time to programming. If
the Excel file is in good shape, you can often manipulate
and import rather than typing in Access.
After setting up tables, always set up formal relationships
between tables to enforce referential integrity.
If possible, write queries to repackage and ask questions
about your data before going on – this will ensure you
have a table design that will eventually work (use
dummy data for this exercise)
Clinical Database Design
Task #3 – Table Structure
Think about final user interface. Does the table structure
look appropriate?
Do all tables have a unique (primary key) field? Is it
logical? If not, add a tablename_id autonumber field.
You really strive to never design a table that doesn’t
have a unique field.
Are all tables related through a primary/foreign key
relationship? You should be able to start at the lowest
data table and work back to high level details following
foreign/primary key links if table relationships are
properly set up (think one to many relationships).
Clinical Database Design
Real-World Exercise
Look at the example spreadsheet and think about table
structures for this project.
Demographics (include one-shot msmts unless this data
will be one set of a larger group of data). The study
number or patient MRN is a logical choice for PK here.
Most measurements that are not one-shot in this study are
repeated with sufficient frequency that we might think
about dumping all data in one other table. The PK
choice here is not as obvious, but we could perhaps
combine the study number with an underscore and a
time code (visit number) to assure we don’t add the
same data for the same patient.
Clinical Database Design
Real-World Demonstration
Clinical Database Design
Real-World Demonstration
Rework Excel File into
sheets for each table with
field names and values.
This should really be done
only after 2-3 cuts at
Excel file, screening form
and flow sheets.
Import sheets for
demographics
and study measurements
into Access.
Clean and check results.
Set up relationships.
Clinical Database Design
User Interface
Your goals in designing a user interface should be:
1) Provide an intuitive interface for users that matches the
logical workflow process.
Help those poor overworked souls do their jobs.
2) Completely protect your database from inappropriate
data entry. Guide the process of data entry.
Don’t let those misfits put garbage into this application.