Distribute n questions to m students with all students having the same questions but in different orders I am building an online exam platform with PHP and MySQL. I have the following requirements for the database:


The exam questions are of MCQ type and there are 20 questions. 
There are 30 students taking the test. 


Is there a way I can have these 30 students receive the 20 questions but in different orders? E.g. that question 1 for Student A would be the question 16 for Student B?
TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) thought processes!

Update: there's an alternative answer provided below.

This proved to be quite tricky. To solve this, I did the following (see fiddle here)

I created tables:

CREATE TABLE test_question
(
  tq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  q_text VARCHAR (256) NOT NULL
);


the text of the questions doesn't really matter for the purposes of this demonstration:

INSERT INTO test_question (q_text) VALUES ('q1');
INSERT INTO test_question (q_text) VALUES ('q2');
INSERT INTO test_question (q_text) VALUES ('q3');
INSERT INTO test_question (q_text) VALUES ('q4');
INSERT INTO test_question (q_text) VALUES ('q5');
INSERT INTO test_question (q_text) VALUES ('q6');


Then the assigned question part:

CREATE TABLE assigned_question
(
  aq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  st_id INTEGER,  -- Student id
  qu_id INTEGER,  -- Question id
  q_text VARCHAR (256) -- text of the question
);


Then, to generate a random list of student_question combinations, i.e. filling the assigned_question table, I used two RECURSIVE WITHs (or CTEs):

INSERT INTO assigned_question (st_id, qu_id)
(
  WITH RECURSIVE student AS (
    SELECT 1 AS s_no
    UNION ALL
    SELECT s_no + 1 AS value
    FROM student
    WHERE student.s_no &lt;= 5 -- 6 students
),
question AS (
    SELECT 1 AS q_no
    UNION ALL
    SELECT q_no + 1 AS value
    FROM question
    WHERE question.q_no &lt;= 4 -- 5 questions
)
SELECT * FROM student
CROSS JOIN question
ORDER BY s_no, RAND()
);


The important bit here is the RAND() function which randomly orders the question part of the result set. You assign a student a random student number and then tell them to select from the master table according to their id number.

Now, I can't guarantee that every student will have a completely different set of questions, but the majority of them should have a different list of questions - if you don't allow them to speak to each other and tell them to answer all questions, in order, even if the answer is blank, then you have a reasonable way of checking that they were answering their own set of questions. The few students who do have matching question lists won't be able to communicate anyway!

then, join this assigned_question table to the test_question table thus:

SELECT aq.aq_id, aq.st_id, aq.qu_id, tq.q_text
FROM assigned_question aq
JOIN test_question tq 
  ON aq.qu_id = tq.tq_id;


Result (sample, see fiddle for full results):

aq_id   st_id   qu_id   q_text
    1       1       2       q2
    2       1       5       q5
 ... results omitted for brevity
    6       2       4       q4
    7       2       3       q3


So, we can see that student 1 will have to answer questions 2 and 5 first whereas student 2 will be answering questions 4 and 3 first (full results below).

This is probably the best you can do in pure MySQL.

Update - single pass query:

I've "fiddled" with this a bit more (pardon the pun! :-) ) and it can in fact be done in one pass, as follows:

(Aside: also see this more elegant fiddle which more closely resembles my second answer).

INSERT INTO assigned_question (st_id, qu_id, q_text)
SELECT s_no, q_no, q_text FROM
(
SELECT * FROM
(
  WITH RECURSIVE student AS (
    SELECT 1 AS s_no
    UNION ALL
    SELECT s_no + 1 AS value
    FROM student
    WHERE student.s_no &lt;= 5 -- students - 6 questions
),
question AS (
    SELECT 1 AS q_no
    UNION ALL
    SELECT q_no + 1 AS value
    FROM question
    WHERE question.q_no &lt;= 4 -- students - 5 questions
)
SELECT * FROM student
CROSS JOIN question
ORDER BY s_no, RAND()
) AS z
JOIN test_question tq ON z.q_no = tq.tq_id) AS a
ORDER BY a.s_no, RAND();


Result:

aq_id   st_id   qu_id   q_text
    1       1       3       q3
    2       1       2       q2
    ... results again omitted for brevity...
    6       2       4       q4
    7       2       3       q3


So, student 1 will answer questions 3 and 2 first and student 2 will answer 4 and 3 first. On inspection, the results I generated with this last fiddle show that every one of the 6 students has the first 2 questions different, so it appears to be working well. Remember however that the RAND() function does not guarantee this and there may be students with the same question order - but with 30 students and 20 questions, this is highly unlikely! HTH! p.s. welcome to the forum! :-)

Alternative Answer:

What you can do is "stagger" (see explanation of results below) the questions asked of the students. All the necessary DDL table construction and DML data is at the bottom of the post - I'll just go through the "core" of the SQL below. Fiddle available here.

SELECT
  ROW_NUMBER() OVER () AS "Num",  -- all records - not strictly necessary
  t.s_id,
  CONCAT
  (
    t.q_id, 
    CASE
      WHEN t.q_id % 100 BETWEEN 11 and 13 THEN "th"
      WHEN t.q_id % 10  = 1               THEN "st"
      WHEN t.q_id % 10  = 2               THEN "nd"
      WHEN t.q_id % 10  = 3               THEN "rd"
      ELSE "th"
    END
  ) AS "Q_ID_2",
  ((s_id + q_id - 2 ) % (SELECT COUNT(*) FROM question )) + 1 AS "Question_id",
  q.question_text
FROM test t
JOIN question q ON ((s_id + q_id - 2 ) % (SELECT COUNT(*) FROM question )) + 1  = q.question_id
ORDER BY s_id, q_id; 


The key bit here is the modulus (%) operator and adding the s_id and q_id. The whole CONCAT bit to get the ordinals is just showing off! :-) Ordinal snippet found here.

Results:

Num     s_id    Q_ID_2  Question_id     question_text
  1        1       1st            1     question_1_txt
  2        1       2nd            2     question_2_txt


So, as we can see, the first student's first question is question_id 1 which is question_1_text. "So what?", you might say, that's not what the original question was aksing!  

But, when we look at the 2nd student's questions we get this:

Num     s_id    Q_ID_2  Question_id     question_text
  9        2       1st            2     question_2_txt
 10        2       2nd            3     question_3_txt


Now, the 2nd student's first question is question_id 2, i.e. question 2 which is not the same as his neighbour's 1st question - which is what the OP asked for!

The cool thing is, if we look at the "interface" between, say students 4 and 5, we get this:

Num     s_id    Q_ID_2  Question_id     question_text
 31        4       7th            2     question_2_txt
 32        4       8th            3     question_3_txt
 33        5       1st            5     question_5_txt
 34        5       2nd            6     question_6_txt


The last questions answered by student 4 are 2 &amp; 3 - i.e. the questions have "wrapped around" - so no student misses any question.

This was really just an intellectual exercise. For (close to) true randomness, my first answer is better - the only advantage I can think of is that it would make distributing the papers easier - all the invigilators (people who supervise exams) would have to do is move one sheet to the back at each new desk. Hope you enjoyed! :-)

=============================== DDL AND DML ================

CREATE TABLE question
(
  question_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  question_text VARCHAR (30) NOT NULL
);

INSERT INTO question (question_text) VALUES ('question_1_txt');
INSERT INTO question (question_text) VALUES ('question_2_txt');
INSERT INTO question (question_text) VALUES ('question_3_txt');
INSERT INTO question (question_text) VALUES ('question_4_txt');
INSERT INTO question (question_text) VALUES ('question_5_txt');
INSERT INTO question (question_text) VALUES ('question_6_txt');
INSERT INTO question (question_text) VALUES ('question_7_txt');
INSERT INTO question (question_text) VALUES ('question_8_txt');



CREATE TABLE student
(
  student_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR (50) NOT NULL,
  UNIQUE (student_name)
);

INSERT INTO student (student_name) VALUES ('student_1');
INSERT INTO student (student_name) VALUES ('student_2');
INSERT INTO student (student_name) VALUES ('student_3');
INSERT INTO student (student_name) VALUES ('student_4');
INSERT INTO student (student_name) VALUES ('student_5');
INSERT INTO student (student_name) VALUES ('student_6');


CREATE TABLE test
(
  test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_id INTEGER NOT NULL,
  q_id INTEGER NOT NULL
);


INSERT INTO test (s_id, q_id) 
SELECT * FROM (
SELECT s.student_id, tab1.question_id
FROM student s
CROSS JOIN (SELECT q2.question_id FROM question q2)
AS tab1) AS tab2 
ORDER BY student_id, question_id;