NetNews Usenet Archive 1993 #1/NN_1993_1.iso/spool/comp/database/oracle/2700

Internet Message Format | 1993-01-06 | 3.9 KB

Path: sparky!uunet!zaphod.mps.ohio-state.edu!sol.ctr.columbia.edu!destroyer!cs.ubc.ca!unixg.ubc.ca!mirek
From: [e-mail] (Miroslaw Piaseczny)
Newsgroups: comp.databases.oracle
Subject: Re: Double data entry comparisons
Date: 7 Jan 1993 00:01:41 GMT
Organization: University of British Columbia, Vancouver, B.C., Canada
Lines: 77
Message-ID: <[e-mail]>
References: <[e-mail]>
NNTP-Posting-Host: unixg.ubc.ca

In article <[e-mail]> [e-mail] 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
>[e-mail]

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
[e-mail]

University of British Columbia