Design logic

[kathy b]

I am creating a clinical research database and I am having trouble with some
of the design logic.

Each Protocol has a unique Budget. Each Budget includes all the Visits
required for the study as well as which medical procedures are completed at
each visit and the corresponding fee. These items are broken down to a per
Patient $ amount. Each Budget also includes overhead applied at the
Protocol level (non-patient specific).

The problem I'm having is how to track the completion and subsequent payment
for each visit for multiple Patients.

Example: As of today, Patient X has completed Visits 1, 2, and 3 and the
corresponding fee is now due. While Patient Y has completed only Visit 1,
with the corresponding fee now due. etc.

Originally I created formProtocol with subformPatients with subdatasheet
Visits. However this design requires the manual entry of the particulars for
each Visit and Medical Procedure for each Patient. (Remember the particulars
are the same for each patient other than completion date.) Is there a less
labor intensive method?

 

[Fred]

I think that you are using "visit" to mean two different things, and to start
treating them separately.

1. The smallest division of work in your Protocol/Budget/Visit
pyramid/heirarchy, and also the smallest unit of billing. Your post also
implies that it has a fixed price.

2. An instance of a patient doing #1

So, continue to use your Protocol/Budget/Visit pyramid to define those
items.

Now make a PatientEvents table which records #2's (each instance of a
patient doing a #1) with prices, plus paymenets received from them (of the
opposite sign) . Include a field which says what it is. It's linked to
#1 and to a patient table. I think that everything you want can be obtained
from that structure.

 

[kathy b]

Thanks! I wasn't seeing the forest for the trees! The PatientEvent table
should solve the problem.