Jump to content

database structure


phppup
Go to solution Solved by Barand,

Recommended Posts

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?

Link to comment
Share on other sites

+-----------+ 
| 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 by Barand
Link to comment
Share on other sites

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 by phppup
Typos
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.