Solved

Database design qestion

Posted on 1998-05-14
5
184 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
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

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

23 Experts available now in Live!

Get 1:1 Help Now