• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • 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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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