Clinical information database design questions
March 30, 2009 5:07 PM Subscribe
I've been tasked with making a large amount of clinical research information we have easier to search and maintain. I have some questions about the best way to organize a database to do this, and in so doing see how much of my time I've wasted so far.
posted by sevenless to computers & internet (8 answers total)
First off I'm using Access 2000--that's what our organization provides. Second, my database skills are marginally more than basic. I know how to make tables, form, queries, etc with Access. I have some idea about how to design relatively simple relational databases, but I'm having trouble determining if what I've done so far with this one is reasonable or if there's something better.
The data I have consists of clinical information for a number of research subjects. The data consists of things that are simple observations (e.g. temperature, weight, etc.) but also things that are more complicated like blood electrolyte measurements that contain many parts for a single "observation." In addition to observations there are also treatments like drug or fluid injections. The data as I get it is basically a list of observations and treatments for each subject organized by day. It's more or less a diary for each subject. A big unsearchable diary.
What I've done so far is to create a number of tables to sort of compartmentalize everything. First I have a table for all the research subjects. For each subject there are 10 or so different observations or types of treatments so I've made tables for all of those. What I've ended up with is a relationship diagram (in Access) that looks like a pinwheel. The field that ties all the tables together is the subject ID. It's present in every table except the ones I use for lookup lists. So, I have lots of one to many links from the subjects table to all my other tables.
Is this the best way to organize this type of log or journal type information? I thought I pretty well understood the concept of normalization and relationships--primary keys, etc.--after going through it again recently, but when I try to apply that to this database it seem that maybe I don't understand it as well as I thought. One problem I'm having trouble with is that there's very little unique in any of the tables' records except the research subject table. I set up artificial primary keys for the tables since for most of them I'd have to use all or most of the fields to identify a record as unique.
I'm thinking there must be a better way to organize the data. Unfortunately I haven't found a discussion anywhere of this kind of database. Or, else I haven't been able to see the many video store and shipping company examples as the same as what I need.