• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Database Table Design MS Access

I will be developing an Access application that will contain patient and medical procedure information.  There are about 20 different procedures that can be done.  For each procedure, we will report the date, patient number, blood pressure, weight, etc. but then each procedure has several measurements that are not common to any of the other procedures.  Do I create a separate table for each procedure (with link to patient ID) or should I create a procedure table with all the measurements?  I'm not sure which is better.  Separate tables would require union queries for reporting/looking up the "whole" patient but one table would contain many fields that would be null in most cases.  

I think an analogy would be a product table.  If I sell computers (with fields for processor, memory, etc.) and hiking shoes (with fields for size, color, etc.), should I keep them in one product table or should I make a table for computers and a table for hiking shoes (or some other variation)?

I am not new to database application development but have usually worked with databases that were already designed.  I have read several of your other postings regarding normalization, etc. but none seems to address this issue.    

Thank you in advance for your help.
0
LynnBertrand
Asked:
LynnBertrand
  • 3
  • 3
1 Solution
 
d1rtyw0rmCommented:
Ok i'm not sure to fully understand you structural problem but.

What i've understood is that you have table ;

Patients (PAT_ID, Name, Adress, age etc.)
PatientProc (PAP_ID, FK_PAT_ID, Date, BloodPressure, Weight)

And MedicalProc that has been done during the PatientProc, and they can be many MedicalProc during a PatientProc ?  (Maybe i'm wrong here but it's what i've understood)

In this case you need a middle table to join PatientsProc to MedicalProc cause a PatientsProc can be linked to many MedicalProc, and many MedicalProc can be linked to many Patient Proc (n-n)

So here we go.

Patients (PAT_ID, Name, Adress, age etc.)
PatientProc (PAP_ID, FK_PAT_ID, Date, BloodPressure, Weight ..etc)
MedicalProc(PAM_ID, Dose, Medicament .. etc)

PatientMedicalProcLink(PMP_ID, PAP_ID, PAM_ID)

So with that structure you can link a PatientProc to many medicalproc via the table PatientMedicalProcLink.

Tell me if i'm wrong ?
0
 
therealmongooseCommented:
Use five tables
Patient - Contains Patient Data
Procedure - Contains names of all Procedures
Measure - Contains all Measures for each procedure, including foreign key of Procedure table that the measure relates to - therefore you only have a record for measures you need to relate to the table - no null fields
Patient_Procedure - link table holding patient id, procedure id, and date
Procedure_Measure - link table linking the patient_procedure record to the measure table, recording patient_procedure ForeginKey, Measure foreign key and result.
 
 
 
0
 
LynnBertrandAuthor Commented:
To therealmongoose,
Okay so I would need one more table in this case as a template for each procedure's measurements (i.e. "you need to record the blood pressure, weight, diameter of this vessel, diameter of that vessel, etc.") and then the form to enter the information would contain a list box of the measurements they need?  (I guess they would then click on each of the measurements in the list box which would bring up a simple screen to enter the measurement?).
Your comments make sense - please just verify that you agree with what I've written above.
Thank you.
 
 
 
 
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
therealmongooseCommented:
You only need one measures table and one procedures table - see the bitmap attached for suggested table design...
 
You can use drop down boxes on forms to select correct procedures and their related measures....
 
Hope this makes sense...

erd.bmp
0
 
LynnBertrandAuthor Commented:
Okay, yes, so basically once we've identified the patient we're dealing with, the entry form would have data based on the Patient_Procedure table and a list box of the corresponding data from the Results table.  I would prefill the Results table with empty records using the Measure table as the template.  
Thank you  
0
 
therealmongooseCommented:
Yes - this is one approach - once the procedure has been allocated to the patient in the patient procedure table, you could fill the results table with the relevant measure against each patient.
 
You'd probably need to have a reversal process so if you allocated an incorrect procedure to a patient, the unwanted records from the results table would be removed....
0
 
LynnBertrandAuthor Commented:
Thank you - you have been extremely helpful (and fast)!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now