Relational design help

[Kurt]

Here is a screen shot of the design for a database used
to track patients who are enrolled in one or more studies:

http://members.cox.net/kheisler6/patient study%
20database.jpg

I need to change the design to accomodate some
situations, but I'm not sure about the best approach.

Situation 1:
------------
There are approximately 50 studies going on, with the
potential for more (i.e. records in tblStudies). Patients
can be enrolled in more than one study. Over the course
of each study, enrolled patients will come in for several
visits. The nature of the visit depends on the study
(i.e., each study has a certain type of visit associated
to it). For example, a person enrolled in the FLU study
will come in and get a shot and get his temperature
taken. A person enrolled in the MILK study will come in
and get his temperature, weight, height taken, but no
shot. Consequently, with 50 studies, there are
potentially 50 unique visit types.

1. Should tblVisits be related to tblStudies (instead of
tblPatients as it is now)?

2. How do I store and manage the visit information, since
the field names are mostly unique for each study? (50+
tables!?!)? Is this a situation where it's best to not
shoot for ideal normalization, and just have a Visits
table with all the potential fields for all studies,
knowing that many of those fields will be blank and
irrelevant for certain patients/studies?

Situation 2:
------------
Currently, tblDoctors is related one-to-many to
tblPatients. tblDoctorGroups is related to tblDoctors via
a junction table. The problem is that the researchers
sometimes know the patient's doctor group, but not the
individual doctor. The current design doesn't permit them
to select a doctor group for the patient.

1. Should I keep everything as is but add a DoctorGroupID
field to tblPatients, and relate that to tblDoctorGroups?

General Question
----------------
Several different researchers will be accessing this
database, mostly to enter visit information when a
patient enrolled in his/her study comes in.

When it comes to setting up forms, the many-to-many
relationships always trip me up. Is this is a good
approach:

1. The main form would be frmPatients. If the researcher
wants to enter information about a patient visit, he
would use frmPatients, which would show:
- general info. about the patient, doctor combo box,
etc.
- a datasheet subform (linked to the main form) to add
or show the study(s) the patient is enrolled in
- a datasheet subform (linked to the studies subform)
to add or show visit information for that selected study)

2. The patient is always a child, who always has one (and
only one) parent associated with him. However, one parent
may have several children enrolled in a study or studies.
So if I'm on Little Joe's record on frmPatients, would I
select (or add if NotInList) his parent from a combo box
of potential parents (from tblParents)? And then do the
same on Little Joe's sister's record?

Or should I instead point the user to a Parents form
where he enters the parent and then enters or selects the
parent's child(ren) (in a datasheet subform, for
example)? This way, when I go back to frmPatients and
look at Little Joe's record, his parent will
automatically be assigned. Is either method prefered?

###

This is a lot so thanks for your time and advice. Kurt

 

[Tim Ferguson]

There are approximately 50 studies going on, with the ....
visits. The nature of the visit depends on the study

Okay: but what about patients ons several studies? Do they get their flu
shot and measurements done in one go? In other words, does a visit relate
to one study at a time or many?

shot. Consequently, with 50 studies, there are
potentially 50 unique visit types.

Probably more? What about InitialAssessment, EndOfCrossOverOne,
EndOfCrossOverTwo, and so on?

Are you planning to capture every datum on every visit, or just the fact of
the visit?

Is this a situation where it's best to not
shoot for ideal normalization,

There is no such thing [1] but in any case you have a load of answers to
work out first.

The problem is that the researchers
sometimes know the patient's doctor group, but not the
individual doctor. The current design doesn't permit them
to select a doctor group for the patient.

In that case you need to work out (a) what you really know and (b) what you
really need to know. Is it better to leave the doctor unknown until you do
know it, or do you really not have to know the docor's name anyway? Who
needs the information, for what purpose, and how much are they prepared to
pay (in user time, complexity, inconvenience for the patients, etc) to get
it?

When it comes to setting up forms,

It seems to me that you are a way off preparing the user interfaces yet...

1. The main form would be frmPatients.

I don't really buy into the idea of "main forms" -- you have to spot the
jobs that people have to do.

The researcher wants a way to design a new study, create the clinical
variable and visit schedules. He won't care about patients at this stage.

The Group Manager needs a method to maintain the Doctors table, and
probably work out SLA payments etc.

The nurse on the front desk needs to register new patients, update old ones
and maintain the Visits tables.

The clinical worker needs to add the clinical data.

The research asst needs to be able to design queries on the data collected,
and clean up the garbage.

etc.

Hope that helps


Tim F

 

[Kurt]

There are approximately 50 studies going on, with

Okay: but what about patients ons several studies?
Do they get their flu shot and measurements done in
one go? In other words, does a visit relate to one
study at a time or many?

Each visit relates to one study at a time. So, for
example, when the patient gets his shot and measurements
on the 2nd visit for the flu study, those data relate
only to that particular visit for that particular study.
On his 3rd visit, he may get some measurements but not a
shot. On his 4th visit, he may get a shot and some
measurements. (In other words, although the experience at
each visit for a certain study varies, it is a
predictable set of events: abc will happen on Visit 1,
abx will happen on Visit 2, ab will happen on Visit 3,
etc.)

Also, a patient can only be enrolled in one study at a
time.

Probably more? What about InitialAssessment,
EndOfCrossOverOne, EndOfCrossOverTwo, and so on?

Good point. tblPatients currently has fields like
Eligible, Interested, and Enrolled, but since this
information is going to be unique to the study a patient
is enrolled in - and not the patient - they should be in
another table (maybe a Screening table, connected to
tblPatients and tblStudies, for instance?).

I'm not sure what you mean by EndOfCrossOverOne, etc.

Are you planning to capture every datum on every
visit, or just the fact of the visit?

Every datum on every visit.

In that case you need to work out (a) what you really
know and (b) what you really need to know. Is it
better to leave the doctor unknown until you do
know it, or do you really not have to know the
docor's name anyway?

The doctor's name is preferred but not required. Some
patients have a particular doctor, others don't (but they
have a group). For example, a patient may not have a
particular doctor; he just goes to the practice and sees
whoever is available. In this case, we know he doesn't
have a doctor, and the doctor group will suffice.

Who needs the information, for what purpose, and
how much are they prepared to pay (in user time,
complexity, inconvenience for the patients, etc) to
get it?

The nurse(s) who meet with patients at each visit need
the doctor name or doctor group info. for two main
reasons: 1) to notify the doctor that a patient is
involved in a study and 2) as an emergency contact. It's
always better if the nurse has a particular doctor on
record, but in cases where the patient doesn't have or
know his doctor, the contact info. for the doctor group
will work.

It seems to me that you are a way off preparing the
user interfaces yet...

Agreed, so I'll save those issues for later. Thanks.

 

[Tim Ferguson]

Also, a patient can only be enrolled in one study at a
time.

I thought you said a patient could be enrolled on more than one. In that
case, you need to key the Visits table on (Patient, Study, VisitDate)

Good point. tblPatients currently has fields like
Eligible, Interested, and Enrolled, but since this
information is going to be unique to the study a patient
is enrolled in - and not the patient - they should be in
another table

In the IsEnrolledIn table: they are attibutes of the relationship between
Patients and Studies. Yes, relationships have attributes too!

I'm not sure what you mean by EndOfCrossOverOne, etc.

Sorry: this is about the nature of medical trials. In a cross over trial,
the kind of information one would collect at the end of a limb would be
different from the sort of information collected during a limb: the same
applies to the initial visit and the end of the trial.

The doctor's name is preferred but not required.

If it's not required, don't put it in. Rule zero: KISS! Rule zero point
one: the more data you put in, the less you can trust any of them. When
John V says, "fields are expensive, records are cheap", it's not just in
computer processing cost: it's measured in credibility as well.

As an aside, in this country the kind of data-collecting you are
contemplating would almost certainly contravene our data protection
regulations!



B Wishes


Tim F

 

[Kurt]

. . .

Thanks for all your feedback.

As an aside, in this country the kind of data
-collecting you are contemplating would almost
certainly contravene our data protection
regulations!

Data will only be collected with informed consent and
full compliance with IRB, HIPAA, and other regulations.

 

[Tim Ferguson]

Data will only be collected with informed consent and
full compliance with IRB, HIPAA, and other regulations.

It seems that European DP legislation is much tighter than USA -- which is
why the rules all had to be bent crazy to allow EU countries to exchange
any computer info with America!

All the best



Tim F