Re: Separate user inputs into a single table?

"AEG" <agibbons_at_erols.com> wrote in message news:u61b42ofbehdef_at_corp.supernews.com...
> I have a table with a primary key of 4 columns. It has a set of
columns
> (A1, A2, A3, etc.)
> and a set of columns (B1, B2, B3, etc.). Each column is to contain
> identical sets of data
> ie, A1 = B1, A2 = B2, A3 = B3, etc. I also have 2 columns to keep
track of
> different
> users A_USER, B_USER. The idea is to have A_USER enter data for the
A
> columns, and the B_USER enter data for the B columns, and then
verify that
> the columns entered by each user match for each record entered. The
main
> requirements are:
>
> 1. User A is not the same as user B, and
> 2. Users are not locked out of records when another user is
accessing the
> database table from
> a data entry form.
>
> So basically, given the main requirements above, my question is:
> Does the above table setup make sense? And do you forsee any
problems with
> accessing
> records in the table?
>
> Basically, the above appears to make sense, but others have
suggested a
> design which puts
> columns A1, A2, A3, etc. and B1, B2, B3, etc. into separate tables.
Code is
> then used to
> compare users and data in the columns in the 2 tables, etc. To me,
this
> seems
> unnecessarily complex. Am I missing something here?

The proposed solution with storing A1/B1 pairs in a single table is brittle (and not normalized but why get technical? :). What happens if you introduce C_USER and/or D_USER?

What I'd suggest you do is something like this:

[user_type] ---> [survey_data]

The [user_type] table is a 'master' table that would contain:

    id NUMBER(16)
    name VARCHAR2(40) -- just making this up... :)... "A_USER", "B_USER" The [survey_data] would contain the data collected for a particular user.

In order to compare two sets of data:

    SELECT count(*)
    FROM survey_data a, survey_data b, user_type ut1, user_type ut2     WHERE a.key = b.key

      AND a.user_type_id = ut1.id
      AND ut1.name = 'A_USER'
      AND a.user_type_id = ut2.id
      AND ut2.name = 'B_USER'
      AND a.col_1 = b.col_2 ...

--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts