database structure

[phppup]

After collecting basic identification info (ie.: name, email, phone) I am having users answer 4 multiple choice questions.

After submitting an answer, the table will update the specific row of Table1.

Not everyone will complete all 4 questions (for a variety of reasons).

I want to have a separate table for individuals that have answered all 4 questions.

My assumption is that it would be more efficient to use a separate table of "qualified individuals" for grading and contact than to unnecessarily evaluate data that is inapplicable.

Perhaps something like;

if ($Question4 != ""){ 
SQL = //copy the row from table1 to table2
}

The REAL issue here is whether I need to list each column name EXCEPT for the auto incremented id OR is there a way to SELECT * (except for column 1 which is id) ?

This will provide me with a table of only fully completed surveys with id numbers that are different from Table1, but sequential.

Or should I just maintain Table1 and not be concerned with rows of incomplete information?

Am I overthinking this? Or making progress on my approaches?

[Barand]

+-----------+ 
| user      |                                                                                    
+-----------+ 
| user_id   |--------+
| name      |        |
| email     |        |      +-----------------+
| phone     |        |      | answer          |                                                                    
+-----------+        |      +-----------------+
                     |      | answer_id       |
                     +----0<| user_id         |
                            | question_no     |                 
                            | selected_choice |
                            +-----------------+

One user table.

Query to find which answered 4 questions

SELECT u.user_id
     , u.name
FROM user u 
     JOIN
     answer a USING (user_id)
GROUP BY u.user_id
HAVING COUNT(answer_id) = 4 

 

Edited May 10, 2022 by Barand

[phppup]

So essentially I'm overthinking and not effecting performance (significantly) by searching through 50 records looking for the 2 users that answered all 4 questions versus establishing a Table2 with only pertinent data?

Edited May 10, 2022 by phppup
Typos

[Barand]

You'll affect performance more, and complicate the queries, by splitting your users across more than one table.

It would also break a rule of DB design - don't store derived data

[phppup]

I should auto increment my main table and then pass that id through to subsequent tables?

In the past, I have used the id as a user_id as well.

If I shouldn't store derived data (which I've seen before and applied) how can I create a system where the first person to complete all 4 questions is retained as cust#1, the second as #2, and so on?

It seems a bit wasteful to seek the person that completed all questions in place 201 out of 2000 partial submissions instead of simply finding cust#201.

[Barand]

Store the test dates - that will tell you who got there first.