Solved

Database design qestion

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create string splitting user defined function in C++ 23 290
USPS intelligent mail barcode font 11 133
Deploying Open Type Font (.OTF) company wide ? 3 84
computer science syllabus 3 70
  Included as part of the C++ Standard Template Library (STL) is a collection of generic containers. Each of these containers serves a different purpose and has different pros and cons. It is often difficult to decide which container to use and …
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 goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
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.

910 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

15 Experts available now in Live!

Get 1:1 Help Now