DATABASE CASE STUDY:

The Puget Sound Health Clinic

CSE100/INFO 100

Fluency with Information Technology

Winter 2005

 

Introduction

This exercise provides a review of database technology and relational database design.  While learning the concepts behind database design is helpful, one can only plan on mastering those concepts by working through an actual project.   This mini-project is intended to improve your understanding of databases and to provide an opportunity to study how the information system of an organization is created to serve its specific needs.  You will not implement the full system.

 

The eventual goal is to build the database application for an imaginary regional health clinic to be called the Puget Sound Health Clinic (PSHC). The clinic provides a variety of services and referrals, but this assignment will be limited to the clinic's lab testing activities. The clinic performs drug testing as well as testing for a variety of diseases: HIV, AIDS, Hepatitis B, etc.  An important aspect of the database system is to assure privacy for the clients. A receptionist and several health professionals staff the clinic.  The database system is intended to support their daily testing activities. 

 

Understanding what things (items, people, events, etc) are important to an organization and need to be tracked is the first step in any database system design. An important first step is to identify how all of those things relate to each other.  By exploring how an organization works and how information flows through it, you can identify those items most important to the system.

Part I: Analysis

Context

In a real world design process, the database developer or team spends many hours interviewing the system users and tracking organization flow in order to identify the many items, events, actions and people to be tracked.  In these interviews a model of the system will be developed.  This section is intended to replace many of those interviews.

 

This database is intended to support the processing of clients who come to the clinic to have a test performed.  During their visit they will give personal information, be seen by a health professional and provide one or more fluid specimens for testing.  The actual tests are not performed at the PSHC, but rather are performed at one or more labs in Seattle.  This means that the specimens must be sent out; when they are returned the client is notified.  It is assumed that the PSHC is a public health clinic, the testing is free and its employees are volunteers.

 

Look at the flow of information through the clinic as a guide to the design.

 

Arrival:

When a client arrives, the receptionist interviews him or her to gather a small amount of personal data that is entered into the client’s record.  If the client has visited the PSHC before, the receptionist simply verifies the information in the existing record. The receptionist is then responsible for scheduling the client with one of the health professionals.  All appointments are for the current day (this is a walk-in clinic) and are set up on a first come, first serve basis.  Any emergency cases would immediately be diverted to the local emergency room and not dealt with in the clinic.

 

Meeting With the Health Professional:

After the client has waited for the health professional to be free, the health professional interviews the patient to find out a brief medical history and the type of test(s) sought.  The health professional fills out a form that is a record of the clinic visit and the discussion.  The form has space for “chart” information as well as check boxes for the tests to be given, including an “exceptional” situation.    

 

If tests are ordered by the health professional (i.e. one or more boxes have been checked) then a specimen (urine, blood, etc.) will be collected from the client to be sent to the lab. A label must be generated to attach to the sample. The label must include the pertinent information about the test and the person requesting it.  To assure privacy a tracking number is used whenever a client must be identified outside the clinic.  The number is not randomly generated, but neither is it easily identifiable with a particular client.

 

Lab Manifest:

Each day the lab test samples will be gathered together. An employee compares all the specimens against a computer generated "lab manifest" listing all of the requested tests by tracking number. After making sure that all specimens are accounted for, each set of test specimens is then grouped (Aids, HepB, etc.).  The same employee will print each individual test manifest, check that all specimens exactly match, and ship them to the appropriate lab with that paper manifest (e.g. Aids).  An electronic version of the manifest may also be sent to the lab.

 

Results Returned:

The lab will return the manifest. The results of each test will be filled in. The outcomes are: positive, negative, failed (must be repeated), inconclusive, and exceptional.  When the results of all tests requested on a particular visit are available, a letter to the patient is generated. In the case that the test failed, the client is encouraged to return to provide another specimen, which will produce a separate visit transaction.  Once the letter is generated, the visit is "closed".  To assist the clinic’s administration and public health officials, summary statistics are generated each quarter. These include the number of clients visiting, and summaries of the outcomes, i.e. number of positives, negatives, etc. in each category.

 

Analyzing all of the previous description will reveal those entities (items, people, actions and events) most important to the clinic that should be tracked.

 

1. What categories of people are mentioned?

 

2. What are the important actions and events that take place?

 

3. What physical items are involved?

 

4. Which people are involved in which actions and items?

 

5. What information does a database system need to maintain in order for the people to accomplish their tasks effectively?  Think about this only in the most general terms.  Specifics will be filled in shortly.


Part II: Design

Identifying Important Entities

The actions and processes described above give us an idea of the items, people, events and their relationships to each other that need to be tracked.  Once identified, they become components of the system.  These and additional components are necessary to support the clinic’s daily operation and should be included in the basic database design.

 

From the Arrival description, it is evident that tracking Client information is very important.  It seem that the database will need to contain

 

In order to help the receptionist to enter information into this table, a form many be appropriate. The form should be attractive, be embellished with the lab’s name and look suitably professional.  Once new client information has been added to the table through use of the form, the receptionist will schedule the client with a health professional.  The scheduling of a visit between a doctor and patient will require opening an additional form that can start a visit record with the appropriate information.  The database system should have a mechanism for doing so.

 

From the Meeting description, it seems that certain important information about each volunteer doctor, nurse, etc. should be maintained:

·         Clinic Volunteer ID (specific to the Health Clinic)

·         First and Last Name

·         Degree (RN, MD, etc)

·         Contact information

·         State health license number

·         Social Security Number

·         Notes (days available to volunteer, etc)

 

There should be a form for entering data for this table that is attractive and consistent in design with the other forms.

 

Also from the Meeting description is the fact that each event of a client seeing a health professional should be recorded.  Relevant data would include:

 

·         The ID for the Visit

·         The client’s ID from the Client table

·         The Health Professional’s ID from the Health Professional table

·         Tests requested (need a field for each test)

·         Test outcome data (need a field for each test outcome)

·         A tracking number

·         Recent Medical History (since last visit)

·         Current Symptoms

·         Remarks/Notes

 

The tracking number is not necessarily the unique record ID for a table.  Instead it is a multi-digit number used to identify the specimens. This number preserves the anonymity of the client while the specimens and tests are being processed outside the clinic. One tracking number is associated with each visit of a client, and so if more than one test is ordered on a single visit, the same tracking number will be used on each. There should be a field to record the outcome of each test, once it is returned.  

 

Identifying Interactions

 

The people involved in the application interact with one another and with the database.  Frequently, the database interactions require a form or a report.

 

The scheduling of a visit between a doctor and patient by the receptionist will require use of a form that can start a visit record with the appropriate client and doctor information. The receptionist needs to view different pieces of data than the health professional.  The Reception desk is only concerned that:

·         The Visit be uniquely identified from any other that day

·         The proper client identification number is noted

·         The proper health professional identification number is noted

 

For the interactions between the client and the health professional, another form is needed for entering the additional data:

·         The Visit identification number

·         The date of the visit (to verify the record is not from another time)

·         The client’s name: first and last

·         The health professional’s name: first and last

·         Health professional certification (RN, MD, etc)

·         A way to show if a test should be ordered

·         The tracking number

·         Notes fields (symptoms, history, remarks, etc.)

For each test requested, a label must be printed to be affixed to the specimen. This label includes the clinic’s name, address and phone number, the date and the tracking number. 

 

From the Lab Manifest description, it is necessary to create a complete manifest of all tests to be sent to the labs with the specimens that:

·         Lists the tracking numbers for all requested tests

·         Shows the checked boxes for each test as confirmation that it was ordered.

 

From the complete manifest each individual test manifest will be created to send to the various labs. Though a paper copy is sent with the specimens, an electronic copy should also be sent to the labs showing the tracking numbers requesting that test.  This way the clinic and the lab can each run a check of specimens against the test manifest.

 

The Results Returned description explains that the lab will return the manifest for each test set submitted to it. The test manifests will come to the receptionist who will enter the results fields each test.  There will only be one of 4 possible results listed (positive, negative, etc). This requires a form for easy data entry by the receptionist.  The information that the receptionist sees, however, should be limited to the tracking number and the outcome field for entering the results. 

 

From the Notification description, there must be a letter generated to the client once all of the tests from a visit have been received. The letter reports the results of each test. If any test’s outcome is "fail" or "inconclusive", a paragraph is inserted recommending that the client return to the lab for a retest. Notice that since a single letter is generated for (possibly) multiple tests, sorting the merged Lab Manifest table on the tracking number field will result in grouping together the tests of one visit. Once the letter and a mailing label have been generated, the record can be closed and moved from the Visit table to the Visit Archive table. Similarly, all returned Lab Manifest table records that have been reported should also be moved to the Lab Manifest Archive table.  A summary statistics report is generated from the Lab Manifest Archive table. The statistics are (a) the number of visits, (b) the number of tests, (c) the numbers of tests of each type and the outcomes. In addition, there should be a summary of the number of visits by month for each month in the quarter.

 

Finally, because of privacy practices in the medical field and possibly state laws, the database must be password protected. Further, it should contain an "auto close" facility that exits the database when no activity has occurred for a while. This will protect unintended access when the health professional leaves the computer.

Design Summary

Based on your understanding of the problem, and the analysis above, record your answers to the questions below.  You should use your own paper, and expect some of the answers to be rather long.  Your answers in this section will count more than the answers in the first part.

 

1. Who are the users of the database?

 

 

 

2. What are the entities of the database?

 

 

 

3. What tables should be defined?  Give their names and a one-sentence description of each.

 

 

 

4. For each table, give its schema

 

 

 

5. What forms are needed?  Give each one a name and a one-sentence description.

 

 

 

6. For each form, state who its users are, and which tables the data comes from.