Solved

Database design qestion

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
C++ Properties One feature missing from standard C++ that you will find in many other Object Oriented Programming languages is something called a Property (http://www.experts-exchange.com/Programming/Languages/CPP/A_3912-Object-Properties-in-C.ht…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
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.

806 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