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?
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 theblood_exam_details
orphysical_exam_details
table?