Ms-access – Database Design Regarding a Relationship for Patient Visit

database-designms access

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?

Best Answer

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.

Related Solutions

Ms-access – Database Relational Design for Multiple Categories

I second @FrustratedWithFormsDesigner's suggestions for a unique constraint. See http://office.microsoft.com/en-us/access-help/create-a-constraint-adp-HP003088257.aspx for how to do this in MS Access.

What a Unique Constraint does is state that no two rows can have duplicate data on some portion of the row. So presumably you want each patient to be in a study only once, so you'd want a unique constraint on PatientsInStudy(participantID, study).

This way each individual can only be given one study id. Similarly in that design your PatientsInStudy table would have a primary key spanning all three fields, and a second unique constraint on (StudyID, study_code). This way even if two studies end up with similar semantics or overlapping spaces, the key cannot be reused in the same study.

Otherwise, good.

Best relationship database design to manage types for different entities

If your various types of individuals and organizations (employees, clients, suppliers) each have multiple phone numbers of various types, then you want to keep the phone numbers in a child table.

This child table should be an intersection between the individual/organization and a phone type table.

Since you have different allowable phone types for each type of legal entity, you can create a table for each of these lists of allowable phone types (your Case 1). The only issue with this is that you may have duplication of data, for example having a "mobile" record in multiple allowable types tables. There is a way to have distinct lists of allowable phone types while at the same time rationalizing these lists for management purposes.

Consider this ERD:

ERD

There is one master lists of phone types (PHONE_TYPE) which includes flags for each possible legal entity type, indicating whether a particular phone type is allowable for each type of legal entity. The table might look like this:

create table PHONE_TYPE
( phone_type_id int primary key
, description varchar(10)
, is_client_type bit
, is_employee_type bit
, is_supplier_type bit
);

From this master list of phone types, you can create distinct views for each legal entity phone type list (i.e. CLIENT_PHONE_TYPE, EMPLOYEE_PHONE_TYPE, SUPPLIER_PHONE_TYPE). One of these views might look like this:

create view CLIENT_PHONE_TYPE
as
select * from PHONE_TYPE
where is_client_type = 1;

This lets you maintain the phone types master list in one place while enforcing the list of allowable phone types for each of the different types of legal entity that you support.

Best Answer

Related Solutions

Ms-access – Database Relational Design for Multiple Categories

Best relationship database design to manage types for different entities

Related Question