Database structure

[Kurt]

I'm developing a database to track patients who are
enrolled in one or more studies. The database will be
large and complex so I wanted to float my ideas here
first to make sure I'm on the right track with the design.

Here's the flow of events for a typical patient (child):

1. A researcher calls the mother to see if her child is
eligible to participate in a study and, if so, if the
mother is interested in enrolling her child in a study.

2. If Yes, the mother and her child (the patient) comes
in to see the researcher. The researcher screens the
child to verify that he's eligible.

3. If Yes, the child is enrolled in the study. Certain
info. is recorded, like the child's DOB, primary doctor,
etc.

4. Over the course of the study, the mother brings her
child in for multiple visits, during which the child gets
a shot, has some measurements (e.g., weight, temperature)
done, etc.

Some assumptions:

1. A study can have many patients, and a patient can be
enrolled in many studies (many-to-many).

2. A doctor can be in more than one doctor group, and a
doctor group can have many doctors (many-to-many).

3. A doctor can have many patients, but a patient can
have only one doctor (one-to-many). (The doctor doesn't
have much to do with the study, so he's not connected to
visits, etc. His info. is recorded mainly as an emergency
contact.)

4. A parent can have many patients, but a patient can
have only one parent (one-to-many)

A screenshot of the preliminary tables and relationships
I've come up with is here:

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

Are suggestions/changes before I proceed?

Kurt

 

[GVaught]

From what I see of the jpg image. I do not see any glaring problems that
jump out. The best predictor is to add one or two bogus records to see if
the structure supports your needs. You can then erase the data, compile the
database to return the any autonumber values back to 1.