I was suggesting that you have two tables, one for data inprogress, and one
for actual live data. It would be easier to deal with, since you wouldn't
have to work with codes or have invalid data in your primary tables. It
would take more tables, but I am generally for more tables, and since they
would be copies of existing tables for the most part, they would be fine.
You could simply have one table with a status code, let the first user enter
the data into the table with a code that says unchecked, then have the
second person key it in. If their data matches, you change the status (and
document the two persons who checked it somehow.) If they don't match, have
a single second table with exceptions that has the keyvalue and a list of
things that are incorrect.
A third alternative would be to use XML to store the forms. You could have
a raw form table that stores any type of form in XML. Then validate the XML
matches (probably an external program required) and then put the data into
the relational structures.
--
----------------------------------------------------------------------------
Louis Davidson - ***@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
Post by Brandon CampbellLouis,
Are you suggesting that I have two tables in the database for each table?.
Unfortunately, the survey that is run a the clinic involves 14 forms and at
least 14 tables that the information is stored in. I have managed to combine
some tables as they were similiar in style and function, but then I also
normalised other tables. Therefore, I still have around 16 tables.
OR
Are you suggesting that I use the entry person as part of the key and
compare the other key values and then write the combined answer to a new
record?
Thank you,
Brandon
Post by Louis DavidsonI wouldn't suggest having two databases, just have a staging queue table. I
agree that you need to have (at least) two users doing heads down data
entry. Then have a third person resolving conflicts.
Transaction
transaction_key <-- the value that identifies the form
other columns
TransactionValidationQueue
transaction_key <-- whatever value you have on the physical form to
identify it
entered_by <-- the person who entered it
other columns
Now the users enter data into the validation queue, you can run a query to
find all rows with matching transaction_keys and all other important columns
matching and you can put them into the transaction table. Any that have
matching transaction_keys but unmatched other data could then be returned in
another query for the verification person to verify and correct.
--
--------------------------------------------------------------------------
--
Post by Brandon CampbellPost by Louis DavidsonSQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
Post by Brandon CampbellMal,
My client will have validation on the fields.
This is my problem currently. We collect data from a clinic (paper forms)
and the data is entered by a person and re-entered and verified by the
second
Post by Brandon Campbellentry person. I feel that there is not enough degree of separation in the
second process.
Therefore, I think that we would need two separate databases that could be
compared and then update the production database.
Do you have any more suggestions?
Thank you,
Post by Mal <<removethis>First idea would be to validate the data on the client side.
If you can't do that, create a mechanism that displays the status of
your
Post by Brandon CampbellPost by Mal <<removethis>data,
like , awaiting verification, and verified... On same DB same table,
same
Post by Brandon CampbellPost by Mal <<removethis>column different values.
Data still awaiting verification, exclude them in selects and
vice-versa.
Post by Brandon CampbellPost by Mal <<removethis>Hope this is a step towards what you were looking for.
Post by Brandon CampbellHello,
I have recently taken over a double data entry database. I feel that
the
right to
and a
Post by Brandon CampbellPost by Mal <<removethis>Post by Brandon Campbellthird person should be a verifier.
Therefore, I was thinking along the lines of having two identical
databases
could be
feel
Post by Brandon CampbellPost by Mal <<removethis>Post by Brandon Campbellthis would be a waste though).
Does anyone have an Idea! I have seen some examples on the internet,
but I
Post by Brandon CampbellPost by Mal <<removethis>Post by Brandon Campbellwould like to hear from others that use this method.
Thank you,
--
--------------------------------------------
Database Administrator
bkc5 AT CDC dot GOV