Snyder 24 May 1999

This project is designed to expand your understanding of databases, and to provide an opportunity to study how the information system of an organization can be customized to better serve its needs.

The task is to build the database and information system for an imaginary Seattle health clinic to be called the Central District Clinic (CDC). Though the clinic provides a variety of services and referrals, this assignment will be limited to the clinic's lab testing activities. It performs drug testing, HIV testing and testing for a variety of other sexually transmitted diseases (STDs). An important aspect of the database system is to assure privacy for the clients.

[Understanding what is to be done is the first requirement of problem solving. The following context describes how the clinic operates, and from this process the database requirements will emerge.]

The focus of the database support is on processing clients who come to the clinic (initial record entry), ordering and tracking the testing (tracking), and contacting the clients to report the results (follow-up). These steps in the process can guide the design.

Arrival: On arrival a client fills out an on-line form or is assisted in doing so. A computer is available to clients enabling them to fill out the form themselves if they wish. Alternatives include the client filling out a paper form that is keyed in by the receptionist, or the receptionist interviewing the client and entering the data. In all cases the receptionist is responsible for assuring that the patient record is fully filled out prior to seeing the health professional. (Repeat visitors naturally do not fill out the form, but its accuracy is checked by the receptionist.)

Meeting The Health Professional : Queuing the client to meet with the health professional is accomplished by placing the client’s chart on his or her desk. The health professional fills out a form that is a record of the clinic visit, and it includes all of the pertinent data from the event, including "chart" information such as medical history, medications, etc. Check boxes are shown on the form for the standard tests provided by the clinic, as well as for an "exceptional" outcome. The tests (and codes) will be drug testing (Drug), HIV, AIDS, syphilis (Syph), gonorrhea (Gono), hepatitis A (HepA) and hepatitis B (HepB).

If tests are ordered by the health professional, i.e. one or more boxes have been checked, then a sample (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 requested test and the person who gave the sample.

Lab Manifest: Each day the lab test samples will be grouped together by test requested. These are compared against a computer generated "lab manifest" that lists all of the requested tests by tracking number. The tests are to be grouped by test requested. The test samples are sent to the appropriate lab with the paper manifest, and an electronic version of the manifest is also sent to the lab. An employee will print the manifest, check that all samples exactly match, and ship the samples to the lab with the manifest.

Results Returned: The manifest will be returned by the lab. The results of each test will be filled in. The results alternatives are: positive, negative, failed --must be repeated, inconclusive -- further testing required.

Notification: A letter is generated in response to the visit. When the results of all tests are available, the letter to the patient is generated. In the case that the test failed, the client is encouraged to return to provide another sample, which will produce a separate visit transaction. In the case that further testing is required, the health professional may also phone the client and explain the situation. Once the letter is generated, the visit is "closed".

To assist the clinic’s administration and public health officials, summary statistics are to be 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 the foregoing description reveals the database components.]

The processes described above imply the basic database design components, which are numbered in the following description.

From the Arrival description, it is evident that (1) a Client table is required. This should contain the client’s name, contact information and birthdate. Additionally, (2) a form is required to assist the client or receptionist in entering this information. The form should be attractive and embellished with the lab’s name to look suitably professional.

From the Meeting description, it follows that there must be (3) a Visit table, containing the pertinent information of the visit. This will include the patient’s ID from the Client table, fields for each test and the tracking number. The tracking number is not the unique record ID, which is also required, but a multidigit 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, and so if more than one test is ordered, the same number will be used on each. There should be a yes/no field for "sent". A (4) form for entering the data will be customized for this table. It will include a check box for each test to be run. For each test requested, a (5) label form 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 for each person requesting the test.

The "chart" information in the Visit table includes notes fields for "presenting symptoms", "medications" and "remarks". Additionally, the first and last name (with certification designation, as in Bill Jones, MD or Carol Jones, LPN) of the health professional who sees the client is included on the Visit data entry form. This implies that there is (6) a health professional (Doctor) table. This table includes a health professional’s ID number, which is used in the Visit table. In addition to the ID, there is the professional’s name, certification designation, contact information, social security number, license number and a notes field. There is (7) a custom form for entering data for this table that is attractive and consistent in form with the other tables.

From the Lab Manifest description, it is necessary to create (8) a table for each test to be sent to the labs with the samples. Each table, known as a Lab Manifest, lists the tracking numbers of all requests for that test for a given day. Though a paper copy is sent with the specimens, an electronic copy should also be sent to the labs to be filled out with the results. So, the Lab Manifest table contains a field for the results of each test, of which there can be the four outcomes.

The Results Returned description explains that the lab will return the Lab Manifest table for all tests submitted to it, and this should be done electronically. The Lab Manifest table will be received by the receptionist who will transfer the file to the database system. The results field will have been filled in for each test. Clearly, Lab Manifests from multiple labs can be merged.

From the Notification description, there must be (9) 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 (10) a mailing label have been generated, the record can be closed and moved from the Visit table to the (11) Visit Archive table. Similarly, all returned Lab Manifest table records that have been reported can also be moved to the (12) Lab Manifest Archive table.

The (13) 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 data base 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 termnal.

The following components of the CDC information clinic were identified in the Needs Analysis section:

1. Client table
2. Client table entry form
3. Visit table
4. Visit table entry form
5. Specimen label
6. Doctor table
7. Doctor table entry form
8. Lab Manifest table for each test, created daily
9. Notification letter to client containing test results
10. Mailing label to client
11. Visit Archive
12. Manifest Archive
13. Clinic quarterly statistics

Notice that key aspects of these components are given in the Needs Analysis above that are not repeated below in the task specification.

The project will create an Access database for the Central District Clinic, complete with reports and appropriate security. The database will be constructed as a "blank database" in Access, and will be named "CDC". The initial password for the database must be: cDc/db (Passwords should contain a combination of lower and upper case letters and other symbols to reduce the chance that they could be guessed.) Remember this password … you’ll need to use it once you declare the database to be password protected.

Two of the three basic tables will be created using the Table Wizard, found by clicking on the "New" button of a blank database’s table tab. Since the tables available in the wizard do not match the CDC's needs exactly, it will be necessary to proceed as follows:

1. Begin with one of the basic tables, which will be renamed once it is set up. For example, the Client table can be built from the Mailing List table’s fields, since one of its main goals is to store contact information to send a letter concerning the test outcomes.
2. Select any appropriate fields from that table that apply.
3. If fields are needed that are not in the starting table, find the field in another table. If the needed field does not exist in any table, find a field that would hold similar data, select it and rename it. For example, suppose you wanted a field to record the neighborhood in which someone lives. (This is an example; neighborhood information is not needed in this project.) There is no such field, but the "city" field is similar enough (it contains text of about the same length), so select it and rename it. Once the database is built, verify that all of these "borrowed" fields are actually of the right type, and if not, revise them.
4. Specify that the wizard is to generate a form for you for the Client table..

The choice of fields for these tables is up to you, but they must be realistic, i.e. the Client table needs a phone number field, but doesn't need a hobbies field. It is as bad to have unnecessary fields as it is to be missing fields. It is necessary to change the name of the table from whatever it is in the base table to the name you need for your system. Notice that the Client and Doctor tables will contain an ID number that is used in the Visit table. Use the same name for this field in each table to help the wizard link the tables together. There should be a field for the current date in Client and Visit tables, which should be generated by the computer from the internal date setting. The Visit table will be constructed from scratch. Other tables will be constructed as explained later.

[Write down your database schema (design) before trying to set it up. List the tables and the fields that they should have to accommodate the Needs Analysis above. Also, you may mess-up the construction of a table or form, do not hesitate to delete it and reconstruct it. The above use of the Table Wizard to create a new table from something that is "close" is a common technique, and should be used whenever it can save effort. It probably doesn't save effort for the Visit table, so it is constructed from scratch.]