Journal of Medical Systems, Vol. 16, No. 6, 1992
TCK: A Clinical Genetics Data Collection System John M. Gersting, John R. Waterson, and Adam M. Gersting
This work examines the database design and user interface design for a clinical genetics data collection system known as TCK. A specific design goal is automatic generation of the CORN reports. Emphasis in this paper is on how the logical data model resulting from the database design, and the user interface work together to enforce the enterprise results pertaining to data. Data screens are shown, sample queries are explained and the data mapping to the CORN reports presented.
INTRODUCTION TCK, like many names for software systems, is an acronym. TCK stands for The CORN Kernel. CORN is also an acronym, standing for Council of Regional Networks for Genetic Services. CORN is funded by the Genetic Services branch of NIH. CORN itself is divided into several geographical regional networks including Great Lakes Regional Genetics Group (GLaRGG). A primary goal of CORN is to collect data regarding clinical genetic services provided in the United States for federal planning purposes. CORN has established a data set for genetic services and distributes annually thirteen forms to genetics clinics nationwide entitled "The Council of Regional Networks for Genetic Services Minimum Data Set Collection Packet." 1 This packet is the major motivation for the development of the TCK system. Manually extracting data from hard-copy clinic files to complete the CORN Minimum Data Set for a year's work is, at best, tedious. To provide a "carrot" to clinics, TCK was developed as an office clinical data system with the "payoff" for CORN being annual report generation. The work presented here concentrates on the enterprise model, the database design From the University of Hawaii at Hilo, Hilo, Hawaii 96720; Children's Hospital Medical Center of Akron; Rose-Hulman Institute of Technology. 297 0148-5598/92/1200-0297506.50/0
© 1992 Plenum Publishing Corporation
298
Gersting et al.
and the user interface design. Implementation details reside in about 20,000 lines of FoxPro code, 2 and the operating instructions fill a 100 page on-line " h e l p " file. Neither of these topics is examined here. Most genetics clinics use the human pedigree drawing as the basis of much of their work but data collection is often based on the individual. Few clinical genetics data collection systems store enough information about individuals to automatically construct a pedigree drawing of their family. Although outside the scope of the GLaRGG specifications, TCK is capable of storing relationships between individuals. This information can be used by a pedigree drawing system such as MEGADATS. 3'4 There are other clinical data collection systems and systems that draw pedigrees. 5 However, we know of no system that provides clinical data collection and produces CORN reports with an interface for drawing pedigrees.
DESIGN PROCESS The design process used in this project was multi-level and involved the users at each level. The levels considered were: • • • • •
Enterprise Statement: an English language problem statement Information Engineering Model: high level diagram of the enterprise Logical Data Model: diagram showing the data items involved in the enterprise Relational Model: relations (tables) used in a relational database design Relational Database Design: database files used in the implementation
For efficiency, we will discuss the first two steps together, the next two together, and the implementation details are considered only briefly.
E N T E R P R I S E S T A T E M E N T AND I N F O R M A T I O N M O D E L The enterprise statement of TCK took the form of a design specification written in technical English. In addition to a description, the specification contained many rules that TCK must obey, for example, "the family is the basic unit of data collection," "every individual must be in a family," " i f an individual is removed (deleted) from the system, all information about that individual's encounters must also be deleted." The enterprise design was captured in an Information Engineering Model (often called an Enterprise Model). A drawing of this model is given in Fig. 1. Each round-ended box represents an entity, a "thing," a " n o u n " which is important to the enterprise. Boxes within boxes represent subtypes. For example, our enterprise needs to know about persons, but persons come in four subtypes: Contact persons-they make appointments (and possibly are responsible for payments); Individuals-they are the patients; M/G/C-MD's, Geneticists, Counselors; they are the clinic staff;
TCK: A Clinical Genetics Data Collection System
1~ i for
( Chart
have
[ )
299
icomists of
~nm.ke,~p
( Fed. Relat. )
Person
C Contect ~C Individual~C MlC/G ~C Other
I1~
d~t5 for Appointment
)
I| perform Xdoneby
)
.)
II want
X o v e n to
( s,.,,~,, ) C z,,~--~,9
-"cor~istsof
i m'rolv'es
for
CEnt ounter
i
supplie,
( r
( L/C/P/I)/O)
~rrom
( Address ) ~or
produces from
C StandardRecord
~o¥
(Irser
)
nerated,from C
'NReports (billing, localreports,...)
)
Figure 1. Information Model Diagram.
Other-anyone else of interest, such as referring physicians, social workers, people who need to see reports. The vertical lines in the diagram in Figure 1 represent relationships, "verbs" or "objects of prepositions." The legend on the figure provides some additional notation: " m a y " indicated by a dashed line, "must" indicated by a solid line, " m a n y " indicated by a "crows-foot" (lack of a crows-foot indicates " o n e " ) . Also, entities at the top of the diagram (families, charts, persons) exist without regard to the TCK system. They are the "subjects" of the system. Items further down the page are "objects" which TCK generates. The words next to the vertical lines are to be read after the entity name. Thus, the top left line in the diagram is read: a family may have a chart (equivalently, a family may have zero to one charts). Going the other way: a chart is for one family. We can read back the enterprise statement from this diagram. The next three paragraphs are an interpretation of the diagram. Subjects and objects appear in bold, words next to relationship lines are in italics, and membership (may or must) and cardinality (one-to-one or one-to-many--the "crows foot") are underlined. The family is the fundamental unit of interest. A family may have one chart. A family may consist of one-to-many related persons. The crows feet around "Ped. Relat." indicate that a person must be a member of one or more family units and a family unit may consist of one-to-many persons. This construction results from the M-to-N relationship between family and person which has been decomposed into a pair of back-to-back one-to-N relationships. A family may have one-to-many appointments. A contact person must make the a p p o i n t m e n t . An appointment may consist of one-to-many encounters. Each e n c o u n t e r is for exactly one individual. Encounters are either clinical en-
300
Gersting et al.
counters or prenatal encounters (sub-types). An encounter may involve many services. An M/G/C may perform many services. Also, an encounter may supply information to others who may want it. Encounters may also involve L/C/P/D/O's (Laboratory tests, Clinical Services, Procedures, Diagnoses, or Other items). Persons may have one-to-many addresses. Persons may have User Info (information of interest to the clinic that is not needed for the CORN reports). The lower portion of the diagram has to do with the reporting functions of TCK. Standard Record is a system-independent representation of information stored by TCK. Encounters must produce one-to-many Standard Records. L/C/P/D/O's produce one Standard Record. Finally, the CORN Reports are generated from many Standard Records. The Information Engineering Model, represented by the drawing in Figure 1, gives a very specific description of the enterprise. The next step is to translate it into a logical data model.
LOGIC AND RELATIONAL MODELS The Logical and Relational Models are database design concepts. Their purpose is to map the enterprise level design down toward implementation. As in the previous models, the concepts of entities (nouns) and relationships (verbs) are fundamental. Since we are discussing the logical and relational models together, although in design theory they are different levels of abstraction, and since the implementation will ultimately be in the form of a relational database, we appeal to intuition and talk about tables (a 2-dimensional layout of information composed of rows, or records, made up of several columns called attributes). For now we will just state the fact that this level of modeling will produce one table for each of our previously defined entities (see reference 6 for more detail). However, we cannot just skip this level and go directly to tables since it has a great deal to do with how the user interface for TCK was designed. We must introduce two important concepts from database design, the primary key and the foreign key. The primary key, a set of attributes for an entity, must uniquely identify each instance (occurrence) within an entity; for example, we must be able to tell two families apart. Foreign keys are the glue that hold a relational design together. They carry the information about relationships. A foreign key in one entity, say Chart, must match with the primary key of its "parent" entity, in this case Family. Practically this might mean that something like a Family_ID could be the primary key (unique identifier) for Family. The attribute Family ID might also appear in Chart--it would be the foreign key "from Chart back to Family." Primary and foreign keys have a great deal to do with how information is added, changed or deleted during the operation of the system. For example, if we try to add a new encounter in TCK, the system checks to see we have an individual for that encounter (TCK simply looks for the foreign key entry for individual in the encounter record). If no individual is found, TCK asks for one. Similarly, we would not be allowed to delete an individual who is "connected" to an encounter. Referring back to Figure 1, these rules are specified by the diagram, where the line from individual to encounter is solid (must).
TCK:
A Clinical Genetics Data Collection System
301
Most database systems, including the one used for the implementation of TCK, do not automatically provide checks like the ones described above. In database terminology these are called integrity rules. These have to be custom coded using the procedural language within the database system. Why bother? If we do not, then the enforcement of all the rules described in Figure 1 (actually the logical data model resulting from Figure 1) is left to the user of the system. This should not be the job of a data entry clerk. TCK does not push this task off to the user. But, if it is handled automatically, how is it done in a "user friendly" way? This is where the design of the user interface becomes important.
USER INTERFACE The user interface was designed in accordance with the guidelines suggested in reference 7. It is in close compliance with the CUA/SSA (Common User Access/Systems Application Architecture) standard, 8 which FoxPro implements fairly closely. This means that TCK is menu and pop-up driven and can be controlled from the keyboard or with a mouse. On-line context sensitive help is available at all times with the touch of the F PersOn "I-- Chart Name : Appointment ~ ]~ I•
[ I
ContactName: Indiv. Name : Gender: DOB:
[
Encounter ~
Individual >
I>
MD/Gen/Cnsl
1
]
MGC Name
:
Other Person Other Name :
Lab/Clin/Pro~/Diag 1~d~r~
Local Code:
Select an item. F i g u r e 2. T C K C o n t r o l S c r e e n .
to
302
Gersting et al.
The design of the interface is based on a navigation scheme called "intelligent pursuit." We ask TCK for what we want in terms of data we know. The interface functionality can be grouped into two major parts: query and data entry. In a query we might ask for an individual by name. Although each entity has a primary key (person number for the person entity occurrences), we never see this number. So, to look at the person Fred Smith, we ask for him by name (and we do not have to look in some hard-copy alphabetical listing of names to find out that Fred's person number is 42). Within data entry we will need to refer to items such as laboratory tests. We can do so by locally defined codes, that is, identifiers that are meaningful, and hopefully memorable. If needed, a popup will appear to help remind us of the available codes.
QUERIES The query activity within the user interface is controlled by the menu pads Data, Select, and Locate (Clear and All Clear clear out information from the fields on the right of the screen). Suppose we wish to locate the information for Gail Martin. To do this, the first step is to pick Select. The result of this pick is shown in Figure 3. On the popup menu that has appeared in the middle of the diagram, we pick I, pointing to Individual. After this, data fields for individual will be highlighted (Figure 4). At this point TCK is asking for information with which to begin a name search. The TCK name search is similar to the one used in MEGADATS 9 which is based on a combination of a Soundex look-up on name with the added conditions based on gender and/or date of birth. In addition, TCK allows "wild card" string searches using ?(wild letter) or *(wild letters). In this example, assume we entered M* for the name (if only one unit is entered for a name it is taken to be a surname) and F for gender. The next step is to pick the Locate Data
Select
Family
I
r
Appointment
I
te Clear All clear Report D> [ ! Date Range P> ~ Pedigree ~ Family ID L$ I '
[ Lab/Clin/Proc/Diag] ~ r ~
Local Code : Date
Figure3. ScreenwithCentralPopupVisible.
Select to
303
TCK: A Clinical Genetics Data Collection System
Locate Clear All clear Report Quit Date Range : Family ID : I Family ] • Pedigree ~ Chart Name : 1 [ >[--Person |-Contact N a m e : , Appointment • ~ Contact ! Data
Select
Encounter 1
I~•
1 l>l
I I
Individual MD/Gen/Cnsl
> Other Person
II
to
Indiv. Name : Gender : ii~ MGC Name Other Name
DOB : iiiiiiiiiiiiiiii~i~
:
Local Code : Lab/Clin/Proc/Diag I Locate an item. Figure 4. Selection Fields are Highlighted.
menu pad. The result is shown in Figure 5. The database has been searched and two choices have been found, Jane Merrill and Gail Martin in the choice window. We would then pick the entry for Gail Martin, which would result in a screen like Figure 4 with "Gaff Martin" under individual name. Although we are concentrating here on queries, we will often want to see the results of the query, that is, the data. To see the data resulting from the query for Gall Martin, we would pick D a t a followed by I to bring up the individual data screen. This screen is shown in Figure 6. All the information on a person screen (in this case for the sub-type individual) is immutable data about that individual, that is, data that are not expected to change. So, once entered, it should not have to be updated often. Note the nickname field at the right of the screen. If a nickname is assigned, like GM in this case, we can use it as a quick look-up by entering the nickname preceded by a single quote, 'GM, in a name field (a "fast path" option for the more experienced user). The items denoted by " T a g for I D " are user definable items, for example, social security number or hospital number. The actual words "Tag for I D " can be changed to read SSN:, Hospital # : , etc. An important feature is found on the line that begins with Address near the bottom of the screen. The number pair [1/3] indicates that Gaff has 3 addresses and we are seeing the first one (445 N 1st S t . . . ). To scan through the others we need only click on the 1. It will change to a 2 and the second address will be displayed. Recall from the data models and from the diagram in Figure 1 that the relationship between person and address is one-to-many. The number pair mechanism [current#/max#] is the way TCK deals with one-to-many relationships. To see the full details of the address, pick on the ADDRESS menu pad on the top line of the screen. Note that pad names in all capital letters indicate that screens will appear, while others produce direct actions. The address screen is shown in Figure 7.
Gersting et al.
304
Individual Choices Nick Gender DOB R O Fam ID Street City St
Name Jane Merrill Gail Martin
F 10/05/40 B N 342 F 12/30/68 W N 250
I Appointment ~
Contact I
1
Contact Name:
,
I Encounter
~
Indiv. Name : M* Gender : F DOB:
Individual I
MD/Gen/Cnsl
MGC Name
:
Other Person]
Other Name
:
Lab/Clin/Proc/Diag
/ /
Local Code : Choose an item.
Figure 5. A Choice Window Appears at the Top of Screen.
The most important feature of the address screen becomes apparent upon data entry. After entering name, title, company and street, the next entry requested is Zip. When the Zip Code is entered, the city, state, county, county# and MSA (Metropolitan Statistical Area) Code values are automatically displayed. This is accomplished using a zip code table. 1° The MSA information is required since one of the items needed for a CORN report is whether the individual is from an urban or rural area. Zero MSA codes indicate the locale is rural. The query we have done for Gail Martin was a very simple query: "Find the individual Gail Martin and bring up her individual data". Both the target, Gail, and the Data
Select
All clear Report Quit Data Date Range : / / to I I Individual Information Old Confirm Locate ADDRESS USER Quit Old
[ New
Locate
Clear
Name: Gail Martin Gender: F Race : W Origin: H Tag Tag Tag Address
Nickname : GM First Name: Gail Mid. Init.: Last Name : Martin Suffix :
Birth Date: 12/30/68 Death Date: / / Cause Code:
for IDI: 123-46-6789 Family ID : 250 for ID2: Individual ID: 2 for ID3: Last changed : 04/11/92 [ i/ 3] 445 N. ist St., Indianapolis, IN 46206 to be done, to quit.
I
/
I
I
[ Lab/Clin/Proc/Diag]
~ d r ~ IUser I
Local Code :
Figure 6. Individual Information Screen.
/
TCK: A Clinical Genetics Data Collection System
Data
New
Select
Old
New
Locate
Confirm
Old
305
All clear Report Quit Data Date Range : / / to Individual Information Locate ADDRESS USER Quit ADDRESS
Confirm
Clear
/
/
Individual Address Information Delete Quit Old
Name : Title : Company: Street : City : State :
Gail Martin
County: County#:
Marion 49
Category:
H
445 N. ist St. Indianapolis IN Zip: 4 6 2 0 6
H o m e Phone: W o r k Phone:
M S A Code:
3480
(iii) 5 5 5 - 1 2 1 2 ( ) < C t r l > < E n d > to be done,
F a m i l y ID : 250 I n d i v i d u a l ID: 2 Last changed : 04/11/92
< E s c > t o quit.
Figure 7. Individual Address Information Screen.
goal, individual data, involve the same entity in the data model (the individual entity). A more complex query is: "Find the individual Gail Martin, and show her encounters". This query starts the same way as the one described above, but after the Locate on Individual, another Locate is done on Encounter which produces a choice display of all the encounters for Gall. If an encounter is chosen, the data for that encounter may be examined by picking Data followed by Encounter. Consider the query: "Find all the blood karaotype laboratory tests (lab ID for this test is BKAR) performed on females in April and May of 1992." We would perform Select operations on Date (entering 04/01/92 and 05/31/92), Individual (entering only gender of F), and Lab/Clin/Proc/Diag (entering local code of BKAR). The Locate part of this process carries out what is called an " a n d " query (date " a n d " gender " a n d " local code) to produce the choices to be displayed in the choice window. The number of " a n d s " is determined by the number of target values we place on the screen with successive Select operations. The more information (target values) we know, the more specific the query will be. Figure 8 shows one choice that resulted from the " a n d " query stated above. Note that all the target value fields (on the right of the screen) are filled in (they are shown in color on the actual screen for highlight, here they are marked on the right with the symbol ~I). Two final points: We may get rid of target values by Clearing them one-by-one or all at once with All Clear. If we pick Report before the query is processed, the results will be saved in a disk file.
DATA ENTRY Each entity (rectangle) shown on Figure 2 has a data entry screen, except for Pedigree, which denotes the entry point to the pedigree processing system. The pedigree system will not be discussed here.
Gersting et al.
306
Data
Select
Locate
I Family I • I Appointment
All clear Report Date Range Pedigree ~ Family ID Chart Name > --Person l-
~--- ~
Clear
Contact J l
1
Quit : 04/01/92to 05/31/92 : 250 • : Martin •
Contact N a m eMartin :Tom
, ]
Encounter
Individual
I
Indiv. Name : Gail Martin Gender: F DOB: 12/30/68
>
MD/Gen/Cnsl ]
MGC Name : Anne Green
> I
Other Person] I
Other Name Mary Jones t MD
I Lab/Clin/Proc/Diag
•
Local Code : BKAR Locate choices for an item.
Figure 8. Completed "and" Query.
We have already seen an example of the Person screen (for the sub-type Individual) and the Address screen in the discussion above. The Family screen collects information about the family (e.g., Family ID and chart name) and the Appointment screen collects information about the appointment (time, date, and place). The User screen may be designed by each clinic to fit its needs. The data collected for the User screen is about a person, usually an individual, and could contain items such as height, weight and/or other information pertinent to that person. In the examples that follow we will look at the encounter and Lab/Clin/Proc/Diag screens. Figure 9 shows the completed Encounter screen for Gail Martin.
New
Old
Confirm
IND
I Encounter
Encounter Information MGC OTHER LCPD Delete
I
Time : i0:00 Date:04/24/92 Location : SBG Type : OCL Clin/Pren: P New/Rep: N Clinical encounter: Reason for referral: Prenatal encounter: Age @ EDC (in years) : Indication for service: Prenatal evaluation :
23.93 CA? NFAF
Snap
Quit
Old
I Family/Appointment/Individual Family: Fam#:250 Chart Name: Martin Appointment: Date: 04/24/92 Time: i0:00 Contact: Tom Martin
I
30 min.
Individual: Name: Gail Martin Gender:F Race:W Orig:H DOB:I2/30/68 , ,
MD / Gen. / Cnslr.[ i/ i]: Anne Green Other Persons [ i/ I]: Mary Jones, MD Lab/Clin/Proc/Diag[ I/ I]L: Local: BKAR Stored: Comment: to be done, to quit.
Figure 9. Encounter Screen.
Date:
/
/
307
TCK: A Clinical Genetics Data Collection System
The block at the upper right of the screen shows information about the Family, Appointment and Individual. Across the bottom of the screen are the one-to-many connections to MD/Gen./Cnslr., Other Persons, and Lab/Clin/Proc/Diag. The material at the upper left, shown on the screen in color, is the information specific to the encounter. Time and date are defaulted from the appointment but can be changed. Recall that an appointment may have many encounters--this is a requirement to be able to generate the CORN reports. If a mother, father and child come to the clinic (for one appointment), and each is examined, there must be at least three encounters recorded for the CORN report output. In Figure 9 the appointment was a Prenatal appointment (necessarily for a female) and is New (the first encounter for this individual). The information found in the Prenatal Encounter portion of the screen is the topic of the next three Figures 10, 11, and 12. These figures show how the tables that support TCK can aid in the data entry efforts. One of the CORN reports requires the age at EDC (Estimated Date of Confinement) for a prenatal patient (mother). When the Age @ EDC (in years) question is reached, we may enter the value if it is known, or ask TCK to perform the computation. Figure 10 shows the special popup window that is used to gather the data for the age at EDC calculation. Note the date of the encounter and the date of birth of the individual are known from earlier data entry and are presented at the top of the window. We need enter only one of LMP (Last Menstrual Period), EDC, or EGA (Estimated Gestational Age of the fetus). From the one value entered the others are computed along with age at EDC. If we are satisfied with the result, Yes is entered after OK? and the age @ EDC is placed on the encounter screen. The response pattern for the next two questions in this part of the encounter screen is typical of TCK's popup menu support for data entry. All data entry is based on sets of user-defined local codes. If we know the local code, we may enter it. TCK will check it against the table of acceptable values for that question. If it is valid, the code is stored.
New
Old
Confirm
Encounter MGC OTHER
IND
Information LCPD Delete
Snap
Quit
Old
I Family/Appointment/Individual Family: Age at EDC Name: M a r t i n Date: 04/24/92 DOB: 12/30/68
I Encounter
I
~--
] !
'ime i ,ocati I :lin/P ~linic I Reaso
Encounter
Enter a value for one of the following, LMP (Date): 02/22/92 • EDC (Date): 11/28/92 EGA (Weeks): 9
Prenat I Age @I Age @ EDC: Indic I Prenatal e v a l u a t i o n
23.93 :
yrs.
OK? Y
e:
g:H
30 min.
DOB:I2/30/68
It
MD / Gen. / Cnslr.[ i/ I]: Anne Green Other Persons [ i/ i]: Mary Jones, MD Lab/Clin/Proc/Diag[ i/ I]L: Local: BKAR Stored: Comment:
i0:00
n
to be done,
to quit.
Figure 10. Age at EDC Calculations.
Date:
/
/
308
Gersting et al.
New
Old
Confirm
IND
Encounter
Encounter Information MGC OTHER LCPD Delete
]
Time : i0:00 Date:04/24/92 Location : SBG Type : OCL Clin/Pren: P New/Rap: N Clinical encounter: Reason for referral: Prenatal encounter: Age @ EDC (in years) : Indication for service: Prenatal evaluation :
23.93
MD / Gen. / Cnslr.[ i/ i]: Anne Other Persons [ i/ I]: Mary Lab/Clin/Proc/Diag[ i/ I]L: Loca Comm
Snap
Quit
old
n Family/Appointment/Individual Family: Fam#:250 Chart Name: Martin Appointment: Date: 04/24/92 Time: Contact: Tom Martin Individual: Name: Gail Martin Primary indication AMA >35 3 5 , CA? are the local codes. We may change these to fit our enterprise (clinic) or to make them easier to remember. The descriptions are also adjustable. There is more to the code tables that is not seen. This is discussed in the next section. We may scroll through the list and pick the item desired. The code picked in this example, CA?, is marked at the right by the symbol ,9.
New
Old
Confirm
IND
Encounter
Encounter Information MGC OTHER LCPD Delete
I
Time : i0:00 Date:04/24/92 Location : SBG Type : OCL Clin/Pren: P New/Rap: N Clinical encounter: Reason for referral: Prenatal encounter: Age @ EDC (in years) : 23.93 Indication for service: CA? Prenatal evaluation : MD / Gen. / Cnslr.[ i/ i]: Anne Other Persons [ i/ i]: Mary Lab/Clin/Proc/Diag[ i/ I]L: Loca Comm
Snap
Quit
Old
I Family/Appointment/Individual Family: Fam#:250 Chart Name: Martin Appointment: Date: 04/24/92 Time: Contact: Tom Martin
i0:00
Individual: Name: Gail Martin Gender:F Race:W Orig:H Prenatal Evaluation NFAF FAF UN IMP UNRE
to be done,
I
30 min.
DOB:I2/30/68
No Fetal Abnorm. Found • I ] ~ Fetal Abnrom. Found Findings Uncertain Results Impossible Eval. Results Unreported to quit.
Figure 12. Prenatal Evaluation.
309
TCK: A Clinical Genetics Data Collection System
Figure 12 shows the popup for Prenatal Evaluation. Here NFAF was chosen. Note the popup is located near the question that produced it. As part of the enterprise model, dictated by the CORN reports, responses cannot be avoided, that is, a blank entry is not acceptable. Each question (that is counted on a CORN report) has codes such as Unknown or Other that we may pick if a suitable specific code cannot be found. Suppose as the next data entry task, we wish to enter a new laboratory test. A pick on the L C P D menu pad will highlight the repeat group pair, [1/1], on the Lab/Clin/Proc/ Diag line at the bottom of the screen. Pressing the (DownArrow) key will change the 1 to a 0 and display the "Create a new LCPD entry for this Encounter" (see Figure 13). Pressing (Enter) at this point will bring up the Lab/Clin/Proc/Diag Information screen (see Figure 14.) Figure 14 shows the L/C/P/D screen partially completed. The New pad is highlighted, and the screen appeared automatically since TCK knows we want a new record by the way this process was launched. A lab test has been selected, type L. We have summoned the local code popup window for this test by pressing CEnter) in response to the Local Code question. Here we are in the middle of the popup choice list, notice the • and • in the bars of the window. As before we may adjust the local codes (e.g., ADNA) and the descriptions (e.g., DNA studies (Amniotic)). The data entry would be completed by entering data and/or a comment. We send a screen of data to TCK by finishing the screen or entering (Ctd)(End) at any time. TCK will respond by highlighting the Confirm menu pad. If we pick this pad, the data will be made part of the database, if not the data will not be saved. At any time during data entry we may leave a screen and return to the menu bar by pressing (Esc). If we wish to end data entry for that screen, the screen may be dispatched by picking Quit.
New
Old
Confirm
IND
Encounter
Encounter Information MGC OTHER LCPD Delete
~
Time : i0:00 D a t e : 0 4 / 2 4 / 9 2 L o c a t i o n : SBG Type : OCL Clin/Pren: P New/Rep: N Clinical encounter: R e a s o n for referral: Prenatal encounter: A g e @ EDC (in years) : 23.93 I n d i c a t i o n for service: CA? Prenatal evaluation : NFAF
Snap
LCPD
~ Family/Appointment/Individual Family: Fam#:250 C h a r t Name: M a r t i n Appointment: Date: 04/24/92 Time: Contact: Tom M a r t i n
i0:00
Individual: Name: Gail M a r t i n Gender:F Race:W Orig:H r
Anne G r e e n M D / Gen. / Cnslr.[ i/ i]: M a r y Jones, MD Other Persons [ i/ i]: Lab/Clin/Proc/Diag[ O~ I]L: Create a new LCPD e n t r y Comment: ,
Quit
;
to pick,
Figure 13. Create a new LCPD entry.
30 min.
DOB:I2/30/68 t
for this Encounter. or .
Gersting et al.
310
New
Old
Confirm
IND
Encounter Information MGC OTHER LCPD Delete
]
Encounter
...., ,
Snap
Quit
LCPD
[ Family/Appointment/Individual
[
Lab/Clin/Proo/Diag Information New
Old
Confirm
Time Locati Clin/P
Name
Clinic
TypeCLc 0 oI: L
Reaso
Prenat Age @ Indic Prena
Delete
Quit
New
: Gail Martin Laboratory Tests
Local Code Stored code
: :
Date COMMENT
: 04/24/ :
AKAR ABIO ACHE ADNA AOTH
in.
"
11
Karyotype (Amniotio) Biochem Anal (Amniotic) ACHE DNA Studies (Amniotic) Other Amniotic Studies
•
to be done, to quit. MD / GI Other L
Lab/clin/Proc/Diag[ O~ I]L: Create a new LCPD entry for this Encounter. Comment: ,
;
Laboratory
to pick,
or .
Tests
Figure 14. L/C/P/D Screen.
As seen above, TCK is very strict, as prescribed by the enterprise model, on what data values may be entered. TCK is also strict, again within the framework of the enterprise model, on the order in which data may be entered. If we start up TCK and as the first request ask to create a new encounter, much activity will ensue. As commanded, the Encounter Screen will come up first. But the model says encounters must have appointments, so the Appointment Screen will come up in front of the Encounter Screen. But, an appointment requires a contact person, so the Contact Person Screen will come up in front of the Appointment Screen. Finally, a person must be in a family so the Family Screen comes up in front of the Contact Person Screen. Now we may start to enter data! Why so complicated? Because, these were the rules agreed to in the modeling process. More importantly, if this is not done the data for the CORN reports will be incomplete. The rules prevent incomplete data and TCK plays by the rules. The other side of the data entry process is the delete process. We are allowed to delete information from the database but the deletes must comply with the data model. For example, an individual cannot be deleted if there is an encounter for that individual in the database. The encounter must be deleted first.
CORN REPORTS A design goal is for TCK to be able to automatically prepare the CORN reports from the data stored in its database. The connections that allow TCK to do this are part of the data tables that carry the information for the popup response windows but are hidden from the day-to-day user. The connection process will be explained with the aid of Figure 15. Figure 15 is a simplified diagram for the TCK data structures that support data entry and map the collected data into the CORN reports. The two columns on the left are what we see in the response selection popup windows. Here we have just two response sets,
TCK: A Clinical Genetics Data Collection System
311
CORN SLOTS for R & G
RACE Local Code W B N A J H O ?
Description
CORN Code
White Black / Negro Native American Asian/Pacific Islander Japanese Hawaiian Other Unknown
R R R R R R R R
CORN Slot 1 2 3 4 4 4 --F 5 6
Code
> > > > >
I ~>
Slot
R R R R R R
1 2 3 4 5 6
Count
42 21 2 5 1 0
Gender M F U ?
Male Female Unknown Unknown
G 1 > G G 2 > G G 3 ~ > G G 3 _ _ I Figu~ 15. LocalCode ~ CORN Count Mapping.
1 2 3
20 50 1
Race and Gender. Suppose CORN wants us to count something they call Caucasian individuals. We decide to call this "White" and use the Local code " W " to represent this fact in the input response set called Race. On the right are the categories mandated by the layout of the CORN reports. There is one "counter" for each reportable item in the CORN reports. That item is given a two part name, a code and a slot number. For example the name of the CORN counter for Caucasian individuals is R, 1. To map the data we collect for what we call W (i.e., White) into the correct (Caucasian) counter, the "address" of the counter (i.e., R,1) is placed in the response entry for W. Note that the relationship between local codes and counters is many-to-one. Say CORN wants to count Asian/Pacific Islander, counter R,4; but we want more detail and wish also to count Japanese and Hawaiian separately. We can do this by adding codes for J and H to the response set. We MUST, however, map these into slot R,4 because CORN does not recognize this distinction. The mapping process allows us to have a custom, or clinic specific, system while still doing what the CORN reports need. All that is necessary is for us to map our concepts onto the counters recognized by CORN. We may always add items that CORN does not have by mapping our item into the "Other" counter in the proper CORN response set. Every data item stored in the TCK database could carry its CORN counter tag everywhere it is stored. A very large database could result. We have avoided this by employing a process within TCK that " m i n e s " the information from the TCK database and produces what we call a Standard Record file. The mining process gathers the data, affixes the CORN counter addresses and records the results in a standard format in the Standard Record file on the disk. The CORN report generation process uses the Standard Record file as input and produces printed reports. Use of the Standard Record file has at least three advantages. It minimizes the size of the stored database (as described above), it could be used as a machine-readable form
312
Gersting et al.
of delivery of the data to CORN, and it can be used as input for reports other than the CORN reports.
CONCLUSIONS The design described here has been implemented using the FoxPro xBase programming language. The screen displays shown above are the actual screens in use. TCK is being tested by a small group of users including members of the GLaRGG data committee. Daily use in the clinic, which is just starting, will produce new input into the enterprise statement. This is usual and anticipated, and it is hoped that the flexibility of the implementation will allow graceful adjustments. Local reporting requirements are a concern as well as interfaces to existing billing systems. Work continues on the integration of the MEGADATS pedigree processor into TCK. They share data structures and use the same screens. The critical issue is legacy data. Legacy data includes both data entered into TCK individual-by-individual without capturing the relationships between the individuals, and data from other systems which have been converted into TCK's structure. The current approach to assimilating the legacy data into the pedigree system is to produce a "naked" pedigree representing the relationships and then decorate this pedigree with the information on the isolated individuals available from TCK.
ACKNOWLEDGMENTS The authors JMG and AMG wish to thank the members of the GLaRGG Data Committee: John Waterson (chair), Harvey Bender, Sandra Davenport, Don Hadley, Joe Hoo, John Naber for their help throughout this project. Also, our thanks to Louise Elbaum and Lorraine Matheson-Rubin for their administrative support, and to Susan Riggle at CORN for answering many questions about the CORN report forms. This work was supported in part by project number MCJ 551004 02 from the Maternal and Child health Bureau (Title V Social Security Act) Health Resources Administration and Services, Department of Health and Human Services.
REFERENCES 1. Council of Regional Networks for Genetic Services, 1991 Minimum Data Set Collection, CORN Data Coordinator, Genetics Disease Branch, 2151 Berkeley Way, Annex 4, Berkeley, CA 94704. 2. FoxPro 2.0, Microsoft Corporation, Redmond, WA 98052. 3. Beidelman, K., and Gersting, J.M., Plotting Human Pedigrees, J. Med. Syst. 9:97-108, 1985. 4. Conneally, P.M., and Gersting, J.M., Medical Genetics Database Management System, Med. Electron. 1986. 5. Mitchell, J.A., Innovations in Human Genetics Education: Using Medical Genetics Applications to Educate for Computer Competence. Am. J. Hum. Genet. 49:1119-1126, 1991.
TCK: A Clinical Genetics Data Collection System
313
6. Fleming, C., and VonHalle, B., Handbook of Relational Database Design, Addison-Wesley, 1989. 7. Smith, S.L., and Mosier, J.N., Guidelines for Designing User Interface Software. MITRE Report ESDTR-86-278, Mitre Corp, Bedford, MA, 1986. 8. Common User Access Panel Design and User Interaction--Systems Application Architecture, IBM Cow. , SC26-4351-0, 1987. 9. Yount, E.A., Conneally, P.M., and Gersting, J.M., Applications of the MEGADATS Database System in Medical Genetics. Am. J. Med. Genet. 28:331-335, 1987. 10. CACI, Marketing Systems, 9302 Lee Highway, Fairfax, VA 22031 (Zips & MSA's in dBase format).
TCK: A Clinical Genetics Data Collection System John M. Gersting, John R. Waterson, and Adam M. Gersting
This work examines the database design and user interface design for a clinical genetics data collection system known as TCK. A specific design goal is automatic generation of the CORN reports. Emphasis in this paper is on how the logical data model resulting from the database design, and the user interface work together to enforce the enterprise results pertaining to data. Data screens are shown, sample queries are explained and the data mapping to the CORN reports presented.
INTRODUCTION TCK, like many names for software systems, is an acronym. TCK stands for The CORN Kernel. CORN is also an acronym, standing for Council of Regional Networks for Genetic Services. CORN is funded by the Genetic Services branch of NIH. CORN itself is divided into several geographical regional networks including Great Lakes Regional Genetics Group (GLaRGG). A primary goal of CORN is to collect data regarding clinical genetic services provided in the United States for federal planning purposes. CORN has established a data set for genetic services and distributes annually thirteen forms to genetics clinics nationwide entitled "The Council of Regional Networks for Genetic Services Minimum Data Set Collection Packet." 1 This packet is the major motivation for the development of the TCK system. Manually extracting data from hard-copy clinic files to complete the CORN Minimum Data Set for a year's work is, at best, tedious. To provide a "carrot" to clinics, TCK was developed as an office clinical data system with the "payoff" for CORN being annual report generation. The work presented here concentrates on the enterprise model, the database design From the University of Hawaii at Hilo, Hilo, Hawaii 96720; Children's Hospital Medical Center of Akron; Rose-Hulman Institute of Technology. 297 0148-5598/92/1200-0297506.50/0
© 1992 Plenum Publishing Corporation
298
Gersting et al.
and the user interface design. Implementation details reside in about 20,000 lines of FoxPro code, 2 and the operating instructions fill a 100 page on-line " h e l p " file. Neither of these topics is examined here. Most genetics clinics use the human pedigree drawing as the basis of much of their work but data collection is often based on the individual. Few clinical genetics data collection systems store enough information about individuals to automatically construct a pedigree drawing of their family. Although outside the scope of the GLaRGG specifications, TCK is capable of storing relationships between individuals. This information can be used by a pedigree drawing system such as MEGADATS. 3'4 There are other clinical data collection systems and systems that draw pedigrees. 5 However, we know of no system that provides clinical data collection and produces CORN reports with an interface for drawing pedigrees.
DESIGN PROCESS The design process used in this project was multi-level and involved the users at each level. The levels considered were: • • • • •
Enterprise Statement: an English language problem statement Information Engineering Model: high level diagram of the enterprise Logical Data Model: diagram showing the data items involved in the enterprise Relational Model: relations (tables) used in a relational database design Relational Database Design: database files used in the implementation
For efficiency, we will discuss the first two steps together, the next two together, and the implementation details are considered only briefly.
E N T E R P R I S E S T A T E M E N T AND I N F O R M A T I O N M O D E L The enterprise statement of TCK took the form of a design specification written in technical English. In addition to a description, the specification contained many rules that TCK must obey, for example, "the family is the basic unit of data collection," "every individual must be in a family," " i f an individual is removed (deleted) from the system, all information about that individual's encounters must also be deleted." The enterprise design was captured in an Information Engineering Model (often called an Enterprise Model). A drawing of this model is given in Fig. 1. Each round-ended box represents an entity, a "thing," a " n o u n " which is important to the enterprise. Boxes within boxes represent subtypes. For example, our enterprise needs to know about persons, but persons come in four subtypes: Contact persons-they make appointments (and possibly are responsible for payments); Individuals-they are the patients; M/G/C-MD's, Geneticists, Counselors; they are the clinic staff;
TCK: A Clinical Genetics Data Collection System
1~ i for
( Chart
have
[ )
299
icomists of
~nm.ke,~p
( Fed. Relat. )
Person
C Contect ~C Individual~C MlC/G ~C Other
I1~
d~t5 for Appointment
)
I| perform Xdoneby
)
.)
II want
X o v e n to
( s,.,,~,, ) C z,,~--~,9
-"cor~istsof
i m'rolv'es
for
CEnt ounter
i
supplie,
( r
( L/C/P/I)/O)
~rrom
( Address ) ~or
produces from
C StandardRecord
~o¥
(Irser
)
nerated,from C
'NReports (billing, localreports,...)
)
Figure 1. Information Model Diagram.
Other-anyone else of interest, such as referring physicians, social workers, people who need to see reports. The vertical lines in the diagram in Figure 1 represent relationships, "verbs" or "objects of prepositions." The legend on the figure provides some additional notation: " m a y " indicated by a dashed line, "must" indicated by a solid line, " m a n y " indicated by a "crows-foot" (lack of a crows-foot indicates " o n e " ) . Also, entities at the top of the diagram (families, charts, persons) exist without regard to the TCK system. They are the "subjects" of the system. Items further down the page are "objects" which TCK generates. The words next to the vertical lines are to be read after the entity name. Thus, the top left line in the diagram is read: a family may have a chart (equivalently, a family may have zero to one charts). Going the other way: a chart is for one family. We can read back the enterprise statement from this diagram. The next three paragraphs are an interpretation of the diagram. Subjects and objects appear in bold, words next to relationship lines are in italics, and membership (may or must) and cardinality (one-to-one or one-to-many--the "crows foot") are underlined. The family is the fundamental unit of interest. A family may have one chart. A family may consist of one-to-many related persons. The crows feet around "Ped. Relat." indicate that a person must be a member of one or more family units and a family unit may consist of one-to-many persons. This construction results from the M-to-N relationship between family and person which has been decomposed into a pair of back-to-back one-to-N relationships. A family may have one-to-many appointments. A contact person must make the a p p o i n t m e n t . An appointment may consist of one-to-many encounters. Each e n c o u n t e r is for exactly one individual. Encounters are either clinical en-
300
Gersting et al.
counters or prenatal encounters (sub-types). An encounter may involve many services. An M/G/C may perform many services. Also, an encounter may supply information to others who may want it. Encounters may also involve L/C/P/D/O's (Laboratory tests, Clinical Services, Procedures, Diagnoses, or Other items). Persons may have one-to-many addresses. Persons may have User Info (information of interest to the clinic that is not needed for the CORN reports). The lower portion of the diagram has to do with the reporting functions of TCK. Standard Record is a system-independent representation of information stored by TCK. Encounters must produce one-to-many Standard Records. L/C/P/D/O's produce one Standard Record. Finally, the CORN Reports are generated from many Standard Records. The Information Engineering Model, represented by the drawing in Figure 1, gives a very specific description of the enterprise. The next step is to translate it into a logical data model.
LOGIC AND RELATIONAL MODELS The Logical and Relational Models are database design concepts. Their purpose is to map the enterprise level design down toward implementation. As in the previous models, the concepts of entities (nouns) and relationships (verbs) are fundamental. Since we are discussing the logical and relational models together, although in design theory they are different levels of abstraction, and since the implementation will ultimately be in the form of a relational database, we appeal to intuition and talk about tables (a 2-dimensional layout of information composed of rows, or records, made up of several columns called attributes). For now we will just state the fact that this level of modeling will produce one table for each of our previously defined entities (see reference 6 for more detail). However, we cannot just skip this level and go directly to tables since it has a great deal to do with how the user interface for TCK was designed. We must introduce two important concepts from database design, the primary key and the foreign key. The primary key, a set of attributes for an entity, must uniquely identify each instance (occurrence) within an entity; for example, we must be able to tell two families apart. Foreign keys are the glue that hold a relational design together. They carry the information about relationships. A foreign key in one entity, say Chart, must match with the primary key of its "parent" entity, in this case Family. Practically this might mean that something like a Family_ID could be the primary key (unique identifier) for Family. The attribute Family ID might also appear in Chart--it would be the foreign key "from Chart back to Family." Primary and foreign keys have a great deal to do with how information is added, changed or deleted during the operation of the system. For example, if we try to add a new encounter in TCK, the system checks to see we have an individual for that encounter (TCK simply looks for the foreign key entry for individual in the encounter record). If no individual is found, TCK asks for one. Similarly, we would not be allowed to delete an individual who is "connected" to an encounter. Referring back to Figure 1, these rules are specified by the diagram, where the line from individual to encounter is solid (must).
TCK:
A Clinical Genetics Data Collection System
301
Most database systems, including the one used for the implementation of TCK, do not automatically provide checks like the ones described above. In database terminology these are called integrity rules. These have to be custom coded using the procedural language within the database system. Why bother? If we do not, then the enforcement of all the rules described in Figure 1 (actually the logical data model resulting from Figure 1) is left to the user of the system. This should not be the job of a data entry clerk. TCK does not push this task off to the user. But, if it is handled automatically, how is it done in a "user friendly" way? This is where the design of the user interface becomes important.
USER INTERFACE The user interface was designed in accordance with the guidelines suggested in reference 7. It is in close compliance with the CUA/SSA (Common User Access/Systems Application Architecture) standard, 8 which FoxPro implements fairly closely. This means that TCK is menu and pop-up driven and can be controlled from the keyboard or with a mouse. On-line context sensitive help is available at all times with the touch of the F PersOn "I-- Chart Name : Appointment ~ ]~ I•
[ I
ContactName: Indiv. Name : Gender: DOB:
[
Encounter ~
Individual >
I>
MD/Gen/Cnsl
1
]
MGC Name
:
Other Person Other Name :
Lab/Clin/Pro~/Diag 1~d~r~
Local Code:
Select an item. F i g u r e 2. T C K C o n t r o l S c r e e n .
to
302
Gersting et al.
The design of the interface is based on a navigation scheme called "intelligent pursuit." We ask TCK for what we want in terms of data we know. The interface functionality can be grouped into two major parts: query and data entry. In a query we might ask for an individual by name. Although each entity has a primary key (person number for the person entity occurrences), we never see this number. So, to look at the person Fred Smith, we ask for him by name (and we do not have to look in some hard-copy alphabetical listing of names to find out that Fred's person number is 42). Within data entry we will need to refer to items such as laboratory tests. We can do so by locally defined codes, that is, identifiers that are meaningful, and hopefully memorable. If needed, a popup will appear to help remind us of the available codes.
QUERIES The query activity within the user interface is controlled by the menu pads Data, Select, and Locate (Clear and All Clear clear out information from the fields on the right of the screen). Suppose we wish to locate the information for Gail Martin. To do this, the first step is to pick Select. The result of this pick is shown in Figure 3. On the popup menu that has appeared in the middle of the diagram, we pick I, pointing to Individual. After this, data fields for individual will be highlighted (Figure 4). At this point TCK is asking for information with which to begin a name search. The TCK name search is similar to the one used in MEGADATS 9 which is based on a combination of a Soundex look-up on name with the added conditions based on gender and/or date of birth. In addition, TCK allows "wild card" string searches using ?(wild letter) or *(wild letters). In this example, assume we entered M* for the name (if only one unit is entered for a name it is taken to be a surname) and F for gender. The next step is to pick the Locate Data
Select
Family
I
r
Appointment
I
te Clear All clear Report D> [ ! Date Range P> ~ Pedigree ~ Family ID L$ I '
[ Lab/Clin/Proc/Diag] ~ r ~
Local Code : Date
Figure3. ScreenwithCentralPopupVisible.
Select to
303
TCK: A Clinical Genetics Data Collection System
Locate Clear All clear Report Quit Date Range : Family ID : I Family ] • Pedigree ~ Chart Name : 1 [ >[--Person |-Contact N a m e : , Appointment • ~ Contact ! Data
Select
Encounter 1
I~•
1 l>l
I I
Individual MD/Gen/Cnsl
> Other Person
II
to
Indiv. Name : Gender : ii~ MGC Name Other Name
DOB : iiiiiiiiiiiiiiii~i~
:
Local Code : Lab/Clin/Proc/Diag I Locate an item. Figure 4. Selection Fields are Highlighted.
menu pad. The result is shown in Figure 5. The database has been searched and two choices have been found, Jane Merrill and Gail Martin in the choice window. We would then pick the entry for Gail Martin, which would result in a screen like Figure 4 with "Gaff Martin" under individual name. Although we are concentrating here on queries, we will often want to see the results of the query, that is, the data. To see the data resulting from the query for Gall Martin, we would pick D a t a followed by I to bring up the individual data screen. This screen is shown in Figure 6. All the information on a person screen (in this case for the sub-type individual) is immutable data about that individual, that is, data that are not expected to change. So, once entered, it should not have to be updated often. Note the nickname field at the right of the screen. If a nickname is assigned, like GM in this case, we can use it as a quick look-up by entering the nickname preceded by a single quote, 'GM, in a name field (a "fast path" option for the more experienced user). The items denoted by " T a g for I D " are user definable items, for example, social security number or hospital number. The actual words "Tag for I D " can be changed to read SSN:, Hospital # : , etc. An important feature is found on the line that begins with Address near the bottom of the screen. The number pair [1/3] indicates that Gaff has 3 addresses and we are seeing the first one (445 N 1st S t . . . ). To scan through the others we need only click on the 1. It will change to a 2 and the second address will be displayed. Recall from the data models and from the diagram in Figure 1 that the relationship between person and address is one-to-many. The number pair mechanism [current#/max#] is the way TCK deals with one-to-many relationships. To see the full details of the address, pick on the ADDRESS menu pad on the top line of the screen. Note that pad names in all capital letters indicate that screens will appear, while others produce direct actions. The address screen is shown in Figure 7.
Gersting et al.
304
Individual Choices Nick Gender DOB R O Fam ID Street City St
Name Jane Merrill Gail Martin
F 10/05/40 B N 342 F 12/30/68 W N 250
I Appointment ~
Contact I
1
Contact Name:
,
I Encounter
~
Indiv. Name : M* Gender : F DOB:
Individual I
MD/Gen/Cnsl
MGC Name
:
Other Person]
Other Name
:
Lab/Clin/Proc/Diag
/ /
Local Code : Choose an item.
Figure 5. A Choice Window Appears at the Top of Screen.
The most important feature of the address screen becomes apparent upon data entry. After entering name, title, company and street, the next entry requested is Zip. When the Zip Code is entered, the city, state, county, county# and MSA (Metropolitan Statistical Area) Code values are automatically displayed. This is accomplished using a zip code table. 1° The MSA information is required since one of the items needed for a CORN report is whether the individual is from an urban or rural area. Zero MSA codes indicate the locale is rural. The query we have done for Gail Martin was a very simple query: "Find the individual Gail Martin and bring up her individual data". Both the target, Gail, and the Data
Select
All clear Report Quit Data Date Range : / / to I I Individual Information Old Confirm Locate ADDRESS USER Quit Old
[ New
Locate
Clear
Name: Gail Martin Gender: F Race : W Origin: H Tag Tag Tag Address
Nickname : GM First Name: Gail Mid. Init.: Last Name : Martin Suffix :
Birth Date: 12/30/68 Death Date: / / Cause Code:
for IDI: 123-46-6789 Family ID : 250 for ID2: Individual ID: 2 for ID3: Last changed : 04/11/92 [ i/ 3] 445 N. ist St., Indianapolis, IN 46206 to be done, to quit.
I
/
I
I
[ Lab/Clin/Proc/Diag]
~ d r ~ IUser I
Local Code :
Figure 6. Individual Information Screen.
/
TCK: A Clinical Genetics Data Collection System
Data
New
Select
Old
New
Locate
Confirm
Old
305
All clear Report Quit Data Date Range : / / to Individual Information Locate ADDRESS USER Quit ADDRESS
Confirm
Clear
/
/
Individual Address Information Delete Quit Old
Name : Title : Company: Street : City : State :
Gail Martin
County: County#:
Marion 49
Category:
H
445 N. ist St. Indianapolis IN Zip: 4 6 2 0 6
H o m e Phone: W o r k Phone:
M S A Code:
3480
(iii) 5 5 5 - 1 2 1 2 ( ) < C t r l > < E n d > to be done,
F a m i l y ID : 250 I n d i v i d u a l ID: 2 Last changed : 04/11/92
< E s c > t o quit.
Figure 7. Individual Address Information Screen.
goal, individual data, involve the same entity in the data model (the individual entity). A more complex query is: "Find the individual Gail Martin, and show her encounters". This query starts the same way as the one described above, but after the Locate on Individual, another Locate is done on Encounter which produces a choice display of all the encounters for Gall. If an encounter is chosen, the data for that encounter may be examined by picking Data followed by Encounter. Consider the query: "Find all the blood karaotype laboratory tests (lab ID for this test is BKAR) performed on females in April and May of 1992." We would perform Select operations on Date (entering 04/01/92 and 05/31/92), Individual (entering only gender of F), and Lab/Clin/Proc/Diag (entering local code of BKAR). The Locate part of this process carries out what is called an " a n d " query (date " a n d " gender " a n d " local code) to produce the choices to be displayed in the choice window. The number of " a n d s " is determined by the number of target values we place on the screen with successive Select operations. The more information (target values) we know, the more specific the query will be. Figure 8 shows one choice that resulted from the " a n d " query stated above. Note that all the target value fields (on the right of the screen) are filled in (they are shown in color on the actual screen for highlight, here they are marked on the right with the symbol ~I). Two final points: We may get rid of target values by Clearing them one-by-one or all at once with All Clear. If we pick Report before the query is processed, the results will be saved in a disk file.
DATA ENTRY Each entity (rectangle) shown on Figure 2 has a data entry screen, except for Pedigree, which denotes the entry point to the pedigree processing system. The pedigree system will not be discussed here.
Gersting et al.
306
Data
Select
Locate
I Family I • I Appointment
All clear Report Date Range Pedigree ~ Family ID Chart Name > --Person l-
~--- ~
Clear
Contact J l
1
Quit : 04/01/92to 05/31/92 : 250 • : Martin •
Contact N a m eMartin :Tom
, ]
Encounter
Individual
I
Indiv. Name : Gail Martin Gender: F DOB: 12/30/68
>
MD/Gen/Cnsl ]
MGC Name : Anne Green
> I
Other Person] I
Other Name Mary Jones t MD
I Lab/Clin/Proc/Diag
•
Local Code : BKAR Locate choices for an item.
Figure 8. Completed "and" Query.
We have already seen an example of the Person screen (for the sub-type Individual) and the Address screen in the discussion above. The Family screen collects information about the family (e.g., Family ID and chart name) and the Appointment screen collects information about the appointment (time, date, and place). The User screen may be designed by each clinic to fit its needs. The data collected for the User screen is about a person, usually an individual, and could contain items such as height, weight and/or other information pertinent to that person. In the examples that follow we will look at the encounter and Lab/Clin/Proc/Diag screens. Figure 9 shows the completed Encounter screen for Gail Martin.
New
Old
Confirm
IND
I Encounter
Encounter Information MGC OTHER LCPD Delete
I
Time : i0:00 Date:04/24/92 Location : SBG Type : OCL Clin/Pren: P New/Rep: N Clinical encounter: Reason for referral: Prenatal encounter: Age @ EDC (in years) : Indication for service: Prenatal evaluation :
23.93 CA? NFAF
Snap
Quit
Old
I Family/Appointment/Individual Family: Fam#:250 Chart Name: Martin Appointment: Date: 04/24/92 Time: i0:00 Contact: Tom Martin
I
30 min.
Individual: Name: Gail Martin Gender:F Race:W Orig:H DOB:I2/30/68 , ,
MD / Gen. / Cnslr.[ i/ i]: Anne Green Other Persons [ i/ I]: Mary Jones, MD Lab/Clin/Proc/Diag[ I/ I]L: Local: BKAR Stored: Comment: to be done, to quit.
Figure 9. Encounter Screen.
Date:
/
/
307
TCK: A Clinical Genetics Data Collection System
The block at the upper right of the screen shows information about the Family, Appointment and Individual. Across the bottom of the screen are the one-to-many connections to MD/Gen./Cnslr., Other Persons, and Lab/Clin/Proc/Diag. The material at the upper left, shown on the screen in color, is the information specific to the encounter. Time and date are defaulted from the appointment but can be changed. Recall that an appointment may have many encounters--this is a requirement to be able to generate the CORN reports. If a mother, father and child come to the clinic (for one appointment), and each is examined, there must be at least three encounters recorded for the CORN report output. In Figure 9 the appointment was a Prenatal appointment (necessarily for a female) and is New (the first encounter for this individual). The information found in the Prenatal Encounter portion of the screen is the topic of the next three Figures 10, 11, and 12. These figures show how the tables that support TCK can aid in the data entry efforts. One of the CORN reports requires the age at EDC (Estimated Date of Confinement) for a prenatal patient (mother). When the Age @ EDC (in years) question is reached, we may enter the value if it is known, or ask TCK to perform the computation. Figure 10 shows the special popup window that is used to gather the data for the age at EDC calculation. Note the date of the encounter and the date of birth of the individual are known from earlier data entry and are presented at the top of the window. We need enter only one of LMP (Last Menstrual Period), EDC, or EGA (Estimated Gestational Age of the fetus). From the one value entered the others are computed along with age at EDC. If we are satisfied with the result, Yes is entered after OK? and the age @ EDC is placed on the encounter screen. The response pattern for the next two questions in this part of the encounter screen is typical of TCK's popup menu support for data entry. All data entry is based on sets of user-defined local codes. If we know the local code, we may enter it. TCK will check it against the table of acceptable values for that question. If it is valid, the code is stored.
New
Old
Confirm
Encounter MGC OTHER
IND
Information LCPD Delete
Snap
Quit
Old
I Family/Appointment/Individual Family: Age at EDC Name: M a r t i n Date: 04/24/92 DOB: 12/30/68
I Encounter
I
~--
] !
'ime i ,ocati I :lin/P ~linic I Reaso
Encounter
Enter a value for one of the following, LMP (Date): 02/22/92 • EDC (Date): 11/28/92 EGA (Weeks): 9
Prenat I Age @I Age @ EDC: Indic I Prenatal e v a l u a t i o n
23.93 :
yrs.
OK? Y
e:
g:H
30 min.
DOB:I2/30/68
It
MD / Gen. / Cnslr.[ i/ I]: Anne Green Other Persons [ i/ i]: Mary Jones, MD Lab/Clin/Proc/Diag[ i/ I]L: Local: BKAR Stored: Comment:
i0:00
n
to be done,
to quit.
Figure 10. Age at EDC Calculations.
Date:
/
/
308
Gersting et al.
New
Old
Confirm
IND
Encounter
Encounter Information MGC OTHER LCPD Delete
]
Time : i0:00 Date:04/24/92 Location : SBG Type : OCL Clin/Pren: P New/Rap: N Clinical encounter: Reason for referral: Prenatal encounter: Age @ EDC (in years) : Indication for service: Prenatal evaluation :
23.93
MD / Gen. / Cnslr.[ i/ i]: Anne Other Persons [ i/ I]: Mary Lab/Clin/Proc/Diag[ i/ I]L: Loca Comm
Snap
Quit
old
n Family/Appointment/Individual Family: Fam#:250 Chart Name: Martin Appointment: Date: 04/24/92 Time: Contact: Tom Martin Individual: Name: Gail Martin Primary indication AMA >35 3 5 , CA? are the local codes. We may change these to fit our enterprise (clinic) or to make them easier to remember. The descriptions are also adjustable. There is more to the code tables that is not seen. This is discussed in the next section. We may scroll through the list and pick the item desired. The code picked in this example, CA?, is marked at the right by the symbol ,9.
New
Old
Confirm
IND
Encounter
Encounter Information MGC OTHER LCPD Delete
I
Time : i0:00 Date:04/24/92 Location : SBG Type : OCL Clin/Pren: P New/Rap: N Clinical encounter: Reason for referral: Prenatal encounter: Age @ EDC (in years) : 23.93 Indication for service: CA? Prenatal evaluation : MD / Gen. / Cnslr.[ i/ i]: Anne Other Persons [ i/ i]: Mary Lab/Clin/Proc/Diag[ i/ I]L: Loca Comm
Snap
Quit
Old
I Family/Appointment/Individual Family: Fam#:250 Chart Name: Martin Appointment: Date: 04/24/92 Time: Contact: Tom Martin
i0:00
Individual: Name: Gail Martin Gender:F Race:W Orig:H Prenatal Evaluation NFAF FAF UN IMP UNRE
to be done,
I
30 min.
DOB:I2/30/68
No Fetal Abnorm. Found • I ] ~ Fetal Abnrom. Found Findings Uncertain Results Impossible Eval. Results Unreported to quit.
Figure 12. Prenatal Evaluation.
309
TCK: A Clinical Genetics Data Collection System
Figure 12 shows the popup for Prenatal Evaluation. Here NFAF was chosen. Note the popup is located near the question that produced it. As part of the enterprise model, dictated by the CORN reports, responses cannot be avoided, that is, a blank entry is not acceptable. Each question (that is counted on a CORN report) has codes such as Unknown or Other that we may pick if a suitable specific code cannot be found. Suppose as the next data entry task, we wish to enter a new laboratory test. A pick on the L C P D menu pad will highlight the repeat group pair, [1/1], on the Lab/Clin/Proc/ Diag line at the bottom of the screen. Pressing the (DownArrow) key will change the 1 to a 0 and display the "Create a new LCPD entry for this Encounter" (see Figure 13). Pressing (Enter) at this point will bring up the Lab/Clin/Proc/Diag Information screen (see Figure 14.) Figure 14 shows the L/C/P/D screen partially completed. The New pad is highlighted, and the screen appeared automatically since TCK knows we want a new record by the way this process was launched. A lab test has been selected, type L. We have summoned the local code popup window for this test by pressing CEnter) in response to the Local Code question. Here we are in the middle of the popup choice list, notice the • and • in the bars of the window. As before we may adjust the local codes (e.g., ADNA) and the descriptions (e.g., DNA studies (Amniotic)). The data entry would be completed by entering data and/or a comment. We send a screen of data to TCK by finishing the screen or entering (Ctd)(End) at any time. TCK will respond by highlighting the Confirm menu pad. If we pick this pad, the data will be made part of the database, if not the data will not be saved. At any time during data entry we may leave a screen and return to the menu bar by pressing (Esc). If we wish to end data entry for that screen, the screen may be dispatched by picking Quit.
New
Old
Confirm
IND
Encounter
Encounter Information MGC OTHER LCPD Delete
~
Time : i0:00 D a t e : 0 4 / 2 4 / 9 2 L o c a t i o n : SBG Type : OCL Clin/Pren: P New/Rep: N Clinical encounter: R e a s o n for referral: Prenatal encounter: A g e @ EDC (in years) : 23.93 I n d i c a t i o n for service: CA? Prenatal evaluation : NFAF
Snap
LCPD
~ Family/Appointment/Individual Family: Fam#:250 C h a r t Name: M a r t i n Appointment: Date: 04/24/92 Time: Contact: Tom M a r t i n
i0:00
Individual: Name: Gail M a r t i n Gender:F Race:W Orig:H r
Anne G r e e n M D / Gen. / Cnslr.[ i/ i]: M a r y Jones, MD Other Persons [ i/ i]: Lab/Clin/Proc/Diag[ O~ I]L: Create a new LCPD e n t r y Comment: ,
Quit
;
to pick,
Figure 13. Create a new LCPD entry.
30 min.
DOB:I2/30/68 t
for this Encounter. or .
Gersting et al.
310
New
Old
Confirm
IND
Encounter Information MGC OTHER LCPD Delete
]
Encounter
...., ,
Snap
Quit
LCPD
[ Family/Appointment/Individual
[
Lab/Clin/Proo/Diag Information New
Old
Confirm
Time Locati Clin/P
Name
Clinic
TypeCLc 0 oI: L
Reaso
Prenat Age @ Indic Prena
Delete
Quit
New
: Gail Martin Laboratory Tests
Local Code Stored code
: :
Date COMMENT
: 04/24/ :
AKAR ABIO ACHE ADNA AOTH
in.
"
11
Karyotype (Amniotio) Biochem Anal (Amniotic) ACHE DNA Studies (Amniotic) Other Amniotic Studies
•
to be done, to quit. MD / GI Other L
Lab/clin/Proc/Diag[ O~ I]L: Create a new LCPD entry for this Encounter. Comment: ,
;
Laboratory
to pick,
or .
Tests
Figure 14. L/C/P/D Screen.
As seen above, TCK is very strict, as prescribed by the enterprise model, on what data values may be entered. TCK is also strict, again within the framework of the enterprise model, on the order in which data may be entered. If we start up TCK and as the first request ask to create a new encounter, much activity will ensue. As commanded, the Encounter Screen will come up first. But the model says encounters must have appointments, so the Appointment Screen will come up in front of the Encounter Screen. But, an appointment requires a contact person, so the Contact Person Screen will come up in front of the Appointment Screen. Finally, a person must be in a family so the Family Screen comes up in front of the Contact Person Screen. Now we may start to enter data! Why so complicated? Because, these were the rules agreed to in the modeling process. More importantly, if this is not done the data for the CORN reports will be incomplete. The rules prevent incomplete data and TCK plays by the rules. The other side of the data entry process is the delete process. We are allowed to delete information from the database but the deletes must comply with the data model. For example, an individual cannot be deleted if there is an encounter for that individual in the database. The encounter must be deleted first.
CORN REPORTS A design goal is for TCK to be able to automatically prepare the CORN reports from the data stored in its database. The connections that allow TCK to do this are part of the data tables that carry the information for the popup response windows but are hidden from the day-to-day user. The connection process will be explained with the aid of Figure 15. Figure 15 is a simplified diagram for the TCK data structures that support data entry and map the collected data into the CORN reports. The two columns on the left are what we see in the response selection popup windows. Here we have just two response sets,
TCK: A Clinical Genetics Data Collection System
311
CORN SLOTS for R & G
RACE Local Code W B N A J H O ?
Description
CORN Code
White Black / Negro Native American Asian/Pacific Islander Japanese Hawaiian Other Unknown
R R R R R R R R
CORN Slot 1 2 3 4 4 4 --F 5 6
Code
> > > > >
I ~>
Slot
R R R R R R
1 2 3 4 5 6
Count
42 21 2 5 1 0
Gender M F U ?
Male Female Unknown Unknown
G 1 > G G 2 > G G 3 ~ > G G 3 _ _ I Figu~ 15. LocalCode ~ CORN Count Mapping.
1 2 3
20 50 1
Race and Gender. Suppose CORN wants us to count something they call Caucasian individuals. We decide to call this "White" and use the Local code " W " to represent this fact in the input response set called Race. On the right are the categories mandated by the layout of the CORN reports. There is one "counter" for each reportable item in the CORN reports. That item is given a two part name, a code and a slot number. For example the name of the CORN counter for Caucasian individuals is R, 1. To map the data we collect for what we call W (i.e., White) into the correct (Caucasian) counter, the "address" of the counter (i.e., R,1) is placed in the response entry for W. Note that the relationship between local codes and counters is many-to-one. Say CORN wants to count Asian/Pacific Islander, counter R,4; but we want more detail and wish also to count Japanese and Hawaiian separately. We can do this by adding codes for J and H to the response set. We MUST, however, map these into slot R,4 because CORN does not recognize this distinction. The mapping process allows us to have a custom, or clinic specific, system while still doing what the CORN reports need. All that is necessary is for us to map our concepts onto the counters recognized by CORN. We may always add items that CORN does not have by mapping our item into the "Other" counter in the proper CORN response set. Every data item stored in the TCK database could carry its CORN counter tag everywhere it is stored. A very large database could result. We have avoided this by employing a process within TCK that " m i n e s " the information from the TCK database and produces what we call a Standard Record file. The mining process gathers the data, affixes the CORN counter addresses and records the results in a standard format in the Standard Record file on the disk. The CORN report generation process uses the Standard Record file as input and produces printed reports. Use of the Standard Record file has at least three advantages. It minimizes the size of the stored database (as described above), it could be used as a machine-readable form
312
Gersting et al.
of delivery of the data to CORN, and it can be used as input for reports other than the CORN reports.
CONCLUSIONS The design described here has been implemented using the FoxPro xBase programming language. The screen displays shown above are the actual screens in use. TCK is being tested by a small group of users including members of the GLaRGG data committee. Daily use in the clinic, which is just starting, will produce new input into the enterprise statement. This is usual and anticipated, and it is hoped that the flexibility of the implementation will allow graceful adjustments. Local reporting requirements are a concern as well as interfaces to existing billing systems. Work continues on the integration of the MEGADATS pedigree processor into TCK. They share data structures and use the same screens. The critical issue is legacy data. Legacy data includes both data entered into TCK individual-by-individual without capturing the relationships between the individuals, and data from other systems which have been converted into TCK's structure. The current approach to assimilating the legacy data into the pedigree system is to produce a "naked" pedigree representing the relationships and then decorate this pedigree with the information on the isolated individuals available from TCK.
ACKNOWLEDGMENTS The authors JMG and AMG wish to thank the members of the GLaRGG Data Committee: John Waterson (chair), Harvey Bender, Sandra Davenport, Don Hadley, Joe Hoo, John Naber for their help throughout this project. Also, our thanks to Louise Elbaum and Lorraine Matheson-Rubin for their administrative support, and to Susan Riggle at CORN for answering many questions about the CORN report forms. This work was supported in part by project number MCJ 551004 02 from the Maternal and Child health Bureau (Title V Social Security Act) Health Resources Administration and Services, Department of Health and Human Services.
REFERENCES 1. Council of Regional Networks for Genetic Services, 1991 Minimum Data Set Collection, CORN Data Coordinator, Genetics Disease Branch, 2151 Berkeley Way, Annex 4, Berkeley, CA 94704. 2. FoxPro 2.0, Microsoft Corporation, Redmond, WA 98052. 3. Beidelman, K., and Gersting, J.M., Plotting Human Pedigrees, J. Med. Syst. 9:97-108, 1985. 4. Conneally, P.M., and Gersting, J.M., Medical Genetics Database Management System, Med. Electron. 1986. 5. Mitchell, J.A., Innovations in Human Genetics Education: Using Medical Genetics Applications to Educate for Computer Competence. Am. J. Hum. Genet. 49:1119-1126, 1991.
TCK: A Clinical Genetics Data Collection System
313
6. Fleming, C., and VonHalle, B., Handbook of Relational Database Design, Addison-Wesley, 1989. 7. Smith, S.L., and Mosier, J.N., Guidelines for Designing User Interface Software. MITRE Report ESDTR-86-278, Mitre Corp, Bedford, MA, 1986. 8. Common User Access Panel Design and User Interaction--Systems Application Architecture, IBM Cow. , SC26-4351-0, 1987. 9. Yount, E.A., Conneally, P.M., and Gersting, J.M., Applications of the MEGADATS Database System in Medical Genetics. Am. J. Med. Genet. 28:331-335, 1987. 10. CACI, Marketing Systems, 9302 Lee Highway, Fairfax, VA 22031 (Zips & MSA's in dBase format).
