3

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?

3
  • 1
    Are there multiple exams per visit? In other words, can a person have a physical exam, a clinical exam, and a lumbar puncture exam in a single visit? Mar 18, 2013 at 18:41
  • @MikeSherrill'Catcall' I think there might be occasions that multiple exams are scheduled for one visit, but that is not the norm. It should also work if the patient schedules more than one exam per visit. It would appear just as another record within the table, like: 03/19/2013 - Patient A - Blood test. 03/19/2013 - Patient A - Physical Exam. something along those lines.
    – alchuang
    Mar 19, 2013 at 17:38
  • @MikeSherrill'Catcall' Sorry, after reviewing and trying to create the tables I'm still confused. If I want to display the "name of the exam", i.e. blood test in the base_exam table, what do I do? Do I make a Lookuptable where all the exams are listed? In terms of inserting record, I plan to have the user enter the date and choose the exam first (thus the lookup table) and then it will display the fields for that particular exam and one can then fill out the data. Is there a better way for the name to be included directly from just the blood_exam_details or physical_exam_details table?
    – alchuang
    Mar 19, 2013 at 18:23

3 Answers 3

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.

10
  • Sorry I think I did not do 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.)
    – alchuang
    Mar 18, 2013 at 17:47
  • That said, I am a little confused. Will the exam_seq_num field be able to link the patient_exams table to the details of the parameter in another table? I think my question is, where would the parameters of each exam be stored? and where would the time stamp of the exam be stored? (lets use physical exam as an example since it is less technical, so height, weight, and other parameters will be stored in a separate table and linked back to the patient_exams table?) Sorry, let me know if you don't understand what I mean.
    – alchuang
    Mar 18, 2013 at 17:53
  • @alchuang: Ok, I've updated to be more in line with your what you're actually asking. Mar 18, 2013 at 18:58
  • Thanks! So essentially, the base_exam table will be like my visits table, containing the timestamp, and linked to the specific exams through the base_exam_id. What if I want the query to display the name of the exam done on that date before revealing the details of the report? For example, on 03/19/2013 Patient A had a physical examination, and then the users can click to see more details since it is linked with the exam table. In that case, should I use just the "name of the examination" as the FK joined to base_exam?
    – alchuang
    Mar 19, 2013 at 14:11
  • Sorry, after reviewing and trying to create the tables I'm still confused. So say if I want to display the "name of the exam", i.e. blood test in the base_exam table, what do I have to do? Do I make a Lookuptable where all the exams are listed? In terms of inserting record, I plan to have the user enter the date and choose the exam first (thus the lookup table) and then it will display the fields for that particular exam and one can then fill out the data. Is there a better way for the name to be included directly from just the blood_exam_details or physical_exam_details table?
    – alchuang
    Mar 19, 2013 at 18:22
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.

7
  • Thanks for the input! So visits will basically be the "middle table" connected to each exam? Won't each exam need a key that links with 'visits'?
    – alchuang
    Mar 18, 2013 at 18:03
  • Visits: visits_id, patient_id, study_id, exam_id, time stamp.. Exam1: exam_id, blood pressure, height, weight...etc. Exam2: exam_id, parameters.....etc. This is the structure I thought of, but that would mean all the rows in exam1 would have the same exam_id, and vice versa for exam2, exam3...all the exams. What would be the best way to normalize this?
    – alchuang
    Mar 18, 2013 at 18:05
  • 1
    Each exam does have a foreign key that references Visits. It's the set of columns {study_name, patient_id, visit_timestamp}. Perhaps I don't understand what you mean by exam. Would you care to try explaining that in words, without any references to tables, columns, keys, etc? Mar 18, 2013 at 18:32
  • 1
    @alchuang: So, one exam per visit. Is that right? Mar 19, 2013 at 14:13
  • 1
    If the patient can schedule more than one exam per visit, the right answer to my question is, "No, Mike, that's wrong." As a rule of thumb, if you answer a database design question with "Yes...but", you really mean "No". In the design sketch I posted, though, there's nothing that would prevent you from recording multiple exams per visit. Each exam just has a foreign key to the table of visits. Mar 23, 2013 at 14:49
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.