Solved

Database design qestion

Posted on 1998-05-14
5
193 Views
Last Modified: 2013-12-03
VCPP 5.0, MFC, DAO, Win95
I have a database design question.  My application is used in the medical community and will contain patient information and test data from a number of different tests. The separation is between the different tests.  Does anyone have any suggestions on if or how I should separate my exam tables from the patient information table.  And what is the best way to link the patient information to the various exam tables.  Should I create a unique exam ID field, for each exam type,  in my patient information table that accesses my different exam data in the exam table? Is there a different and better way to link the data in my tables? All suggestions welcome.  
0
Comment
Question by:Surfer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Accepted Solution

by:
econde earned 100 total points
ID: 1177367

I think you should have three tables with these sample fields:

      1 - Patients info:
            patient id
            name
            age
            etc ...

      2 - Test log
      
            patient id
            exam id
            date
            result
            etc ...

      3 - Exam info

            exam id
            description
            cost
            etc ...

To query the tests done by John in March...

      Select description
      from exam_info, test_log, patients
      where exam_info.exam_id = test_log.exam_id
      and test_log.patient_id = patients.exam_id
      and name = "John"
      and date between '01-mar-98' and '31-mar-98'


0
 

Author Comment

by:Surfer
ID: 1177368
That is good information.  Using your example, what would be the best way to link the similar fields in the 3 tables.  For instance , in Table 1 and 2, how would I programmatically “link” the patient id field?  Do keep track of the indexes myself or is there a way to create the patient id field in Test log and “link” it to the patient id field in Patient info?  Thanks again.
0
 

Expert Comment

by:econde
ID: 1177369
   If you use a relational database like Access, Oracle or Informix, the job is pretty easy. Using SQL (as you can see in my previous example), the database engine will take note of the link because you are doing that in the query.

      Let me show you in an easier example, to query the results of the entire tests done for John:

      Select test_log.results
      from test_log,patient_info
      where test_log.patient_id = patient_info.patient_id
      and patient_info.name="John"

I suggest a quick read of a SQL book. It's pretty easy and will help you a lot. You can also take a look to Access, a very easy database for small applications where you can create the links in a graphic and very simple way.

Of course, you don't need to keep track of the indexes for yourself.

0
 
LVL 1

Expert Comment

by:cph
ID: 1177370
You should read a book on database design to acquire a basic understanding of the DBs.
Here his a book, you may want to check:
Fred R. McFadden and Jeffrey A. Hoffer, Modern database management, fourfth edition.1994. ISBN 0-8053-6047-6

There are others like, Database systems: Management and Design (ISBN 0-87835-237-9) which aer also worth a look....

CpH
0
 
LVL 4

Expert Comment

by:sganta
ID: 1177371
Hai
Using econode solutions I am slightly modifying in the tables

TABLE1 - Patients_Info:
    patient id  : PRIMARY KEY
    name
    age
    etc ...

TABLE 2 - Exam_info
    exam id  : PRIMARY KEY
    description
    cost
    etc ...

TABLE 3 - Test_log

    patient id  FOREIGN KEY Patient_Info(patient_id)
    exam id   FOREIGN KEY Exam_Info(exam_id)
    test_date
    result
    etc ...
    PRIMARY KEY(patient_id,exam_id,test_date);
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by John Humphreys C++ Threading and the POSIX Library This article will cover the basic information that you need to know in order to make use of the POSIX threading library available for C and C++ on UNIX and most Linux systems.   [s…
This article shows you how to optimize memory allocations in C++ using placement new. Applicable especially to usecases dealing with creation of large number of objects. A brief on problem: Lets take example problem for simplicity: - I have a G…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

726 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