Solved

Database Table Design MS Access

Posted on 2009-05-15
7
325 Views
Last Modified: 2012-05-07
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
Comment
Question by:LynnBertrand
  • 3
  • 3
7 Comments
 
LVL 2

Expert Comment

by:d1rtyw0rm
ID: 24396657
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
 
LVL 10

Expert Comment

by:therealmongoose
ID: 24396699
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
 

Author Comment

by:LynnBertrand
ID: 24396866
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:therealmongoose
ID: 24396972
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
 

Author Comment

by:LynnBertrand
ID: 24397056
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
 
LVL 10

Accepted Solution

by:
therealmongoose earned 500 total points
ID: 24397215
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
 

Author Closing Comment

by:LynnBertrand
ID: 31581959
Thank you - you have been extremely helpful (and fast)!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now