Database Design Regarding a Relationship for Patient Visit

Question

I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have.

Busines rules

• There are a total of 6 different studies, and patients can be categorized under more than one study if they fit the criteria.

• Once they are under a study, they are required to pay routine visits for various type of exams (a total of 10 different kinds), i.e. clinical exams, physical exams, and other examinations to keep track of information.

• Each of these various exams must be time stamped to allow back tracing.

• In addition, patients are required to complete these exams up to three times while in the study, and each examinations has 10+ parameters that needs to be recorded as well.

So lets say there are 100 patients, these patients are categorized into studies A, B, C, D, E, F, with the possibility of being in more than one studies at a time (i.e. patient_25 can be in studies A,B, and E). Each study requires 3-8 different patient visits to collect information from examination, and some studies require the examinations to be performed up to a total of 3 times over a period of time.

What would be the best way to create tables that fit this kind of structure? Here's my thoughts so far, let me know if there is a better way.

My representation with tables so far

Tier 1.

• Studies Table: study_id(primary key), study_name, study_patient_code, patient_id(foreign key)

Tier 2.

• Patient Demographics: patient_id(primary key), patient_name, patient_gender, patient_dob...etc.

Tier 3.

• Visits Table: visit_id (primary key), patient_id (foreign key), visit_date, visit_type

Tier 4.

• Physical Exam: physicalexam_id (primary key), visit_id (foreign key), pe_height, pe_weight, pe_score...etc.

• Clinical Exam: clinicalexam_id (primary key), visit_id (foreign key), parameters....etc

• Examination 3: exam3_id (primary key), visit_id (foreign key), parameters....etc

• Examination 4: exam4_id (primary key), visit_id (foreign key), parameters....etc

.....

• Examination 10: exam10_id (primary key), visit_id (foreign key), parameters....etc

---

So basically, the study table will be linked to patient demographics table. the patients table will be linked to visits, which will then be linked to various examination tables with columns consisting of the parameters recorded on the date of visit. Is this a viable method of approaching this problem? Or are there a more elegant way of doing so?

I hope this makes sense, if not, let me know and I'll be sure to elaborate more.

---

Additional information

I apologize for not doing a good job of explaining myself. So there are various type of exams that returns on average about 15-20 fields of data that needs to be stored. Each exam has its own purpose so the parameters will be different. For example, one exam may be just for Lumbar Puncture and contains fields with specific information with regards to that, physical exam will be for height, weight, blood pressure, BMI...etc. Patients may have to comeback for an exam multiple times during a study (for example, need to do physical exam on 1st, 2nd, 3rd visit.)

I guess what I'm trying to ask is where should the parameters of each exam be stored? and where should the time stamp be stored? How should I organize the tables so that we normalize the database for each visit without excessive repetition of information for different exams and the 1st, 2nd, 3rd visits?

Answer 1

The first thing that catches my eye is what appear to be separate tables for each exam. This could get rather difficult to maintain in the future: if the structure of exams changes you need to update n tables. Also, any aggregate queries that cover all exams for 1 patient will need to join n tables.

I'd suggest a structure like this:

patient_exams
-------------
  patient_exam_id (PK)
  visit_id (FK)
  patient_id (FK)
  study_id (Fk)
  exam_seq_num 
  (other fields)

Use exam_seq_num to track which exam number the record is for a patient. You can use a composite key containing patient_id, visit_id, study_id, exam_seq_num to ensure that you don't get exams for a patient with duplicate sequence numbers. You'll still need a bit of code to create the correct sequence number, maybe an on-insert trigger.

---

UPDATE:

Ok, so now it's clear that the exame tables are actually for different types of exams. You could have something like

base_exam
---------
  id (PK)
  patient_id (FK)
  exam_date
  (other stuff)

Lumbar_exam_details
-------------------
  lumbar_exam_id (PK)
  base_exam_id (FK to base_exam.id)
  (other specific fields)

blood_exam_details
------------------
  blood_exam_id (pk)
  base_exam_id (FK to base_exam.id)
  (other specific fields)

All of your exam detail tables reference the base_exam table, which stores common fields for all exams (such as the date of the exam, the patient who was examined, etc...).

---

If you really want to have a "display name" for exam types, I would do that in a view that overlays the specific exam table. For example, the query for lumbar_exam_view might look like:

SELECT *, "Lumbar Exam" AS DISPLAY_NAME
FROM LUMBAR_EXAM_DETAILS

Use this view in any queries/reports on lumbar_exam_details you will have access to display_name anywhere that you want the user-friendly string.

If you need the display name to be stored as actual data, you can add an exam_type_id field to base_exam and then have it point to an exam_type table:

exam_type
---------
  id
  display_name

Data:

exam_types
ID  | display_name
------------------
1   | Lumbar Exam
2   | Blood test

Now your base exam records have an ID that points them to the correct user-friendly string. Note that this does not ensure that the exam detail record is of the correct type (i.e. it is possible, for example, to have a base_exam record that is referenced by lumbar_exam_details, but the base_exame record erroneously references the display name "Blood Test") - it only works on the display name.

Answer 2

In the long run, thinking in terms of tiers and of ID numbers is more likely to hurt you rather than help you. At the start, use ID numbers only for those things that don't carry their identity with themselves. Studies should carry their identity with themselves; that is, their names should almost certainly be unique. Patients (people) don't carry their identity with themselves; there are many people named "John Smith".

My guess at primary keys is in parens. Foreign keys should be obvious.

Studies (study_name)
Patients (patient_id)
StudyPatients (study_name, patient_id)
Visits (study_name, patient_id, visit_timestamp)
PhysicalExam (study_name, patient_id, visit_timestamp)
ClinicalExam (study_name, patient_id, visit_timestamp)

It's not clear whether you need any additional columns to identify rows in PhysicalExam and in ClinicalExam. It doesn't look like it on first glance, though. Later, if there's sufficient interest and if I have enough time, I'll write this in SQL.

Answer 3

First your problem is that you are using Access which is not going to perform well with the struture you need.

Your problem is most commonly solved using what is called an EAV table. This is because you will probaly be adding more and more exam typoes weach of which will have differnt parameters.

I would structure it so that I had all the common details about the patient in a patient table (and some related tables for things that change over time or that have mulitple values such as address).

I would then Have an exam table with dates and a study table with the current studies. Exams patient and studies would all relate through join tables (patientid and studyid inthe patient study table and examid and studyid in examStudy table and patientid and examid in the patient Exam table). This is becasue you ahvea many to many relationshsip between these things.

Now as to the exam details this is where the EAV table comes in. It would include Examid, parameterName, parameter value) You would then inseter a record for each value you want to store. That way you can add and change them as the exams change. They are harder to query this way but this is exactly the use case that EAV tables were designed to meet, frequently changing values that can't be knwon in the original design. Access may not handle this well, so you might want to consider a nosql database for this part.

And of course you need to protect patient information according to HIPAA rules. Be very sure you are aware of them and how to prtect the data. Again Access may not be the best choice for that unless you don't intend to store client names and addresses etc at all.