Re: Double data entry comparisons

In article <1993Jan4.154617.11411_at_macc.wisc.edu> dly_at_vms.macc.wisc.edu writes:
>I have a database design question on double data entry comparisons.
>If it matters, we will be using ORACLE 7.0 (we haven't got it yet),
>SQL*FORMS 3.0, on the Sun platform.
>
>We will have different data entry clerks entering the same data.
>Then we would like to compare only selected fields. If there are
>no disagreements, then the data is ready. If not, then the data
>is not ready for analysis (we have a manual arbitration step here).
>
>The reason why I say selected fields, is that we will not be comparing
>the long text fields (comments, etc). Nor will we be comparing the
>auditing information (clerk id, time of start, time of post, etc).
>
>I want to make sure that I can make comparisons fairly easily (there
>will be about 30 different types of data entry forms, each with
>6 to 80 fields).
>
>So, is it easier to have just the fields to be compared in one table,
>and the auditing information in another?
>
>Should I have two different tables for each form, one for the first
>data entry, the second for the second data entry?
>
>I would appreciate any ideas. Especially, if you have any stories,
>good or bad about similar designs.
>
>Thank you!
>Debbie Yoshihara
>Department of Biostatistics
>DLY_at_MACC.WISC.EDU

You can probably find several ways of doing the double data entry. In most cases you have to create some temporary tables, and almost always you have to store both, the original, and the verified values of the records. That may be pretty depressing when you are dealing with hundreds of tables having substantial number of records.

For our clinical trials application we have implemented method where each record is stored only once. The status of the record is determined by value of the data field called ENTERTIMES. Value of the field (1 or 2) shows if the record was only initially entered, or was verified. All the changes, corrections and updates are stored in the global audit table.

All data entry is done using SQLForms3.0 screens. Both, the initial, and the verification data entry are done using the same forms. The mode of the form (initial or verification) is controlled by the global variables, and is established when the form is called from the menu.

The verification entry is being done on the initially entered records. When a key for the record has been entered, the corresponding record is queried from database. The operator however, doesn't see values of the fields to be verified. They are 'hidden', i.e. displayed with the 'hide' display attribute.

The 'hide' display attribute has been created using oraterm and displays red on red. In our case (we use unix-pc's for the data entry) that works fine, but I'm not too sure about portability of this method to some other terminals (like VT100).

The actual verification is handled by the on-new-field-instance, and on-new-field-instance triggers where the newly entered value is compared to the original value, and the attribute of the field is changed back to 'normal'.

Also, we allow to specify what fields are to be verified. To mark fields for the second entry we use the query_length attribute of the field. All selected for that purpose fields have query_length greater then 100. These fields, when the form is called in the verification mode, and the record hasn't been verified yet, are visible as blank red boxes.

Hope that helps,

Mirek Piaseczny
mirek_at_unixg.ubc.ca

University of British Columbia Received on Thu Jan 07 1993 - 01:01:41 CET