Database design qestion

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.  
SurferAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
econdeConnect With a Mentor Commented:

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
 
SurferAuthor Commented:
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
 
econdeCommented:
   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
 
cphCommented:
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
 
sgantaCommented:
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
All Courses

From novice to tech pro — start learning today.