Alternative to Storing ID List in SQL Database

Question

tl;dr: need to store arrays of ids of one table in a second table but all I can find are messy solutions.

I have the following database tables for a quiz system I'm designing (based on the top answer to this question):

Quizzes
   -quiz_name TEXT PRIMARY KEY NOT NULL

Questions
   -id INTEGER PRIMARY KEY AUTOINCREMENT
   -quiz_name TEXT REFERENCES Quizzes(quiz_name)
   -prompt TEXT NOT NULL
   -difficulty TEXT NOT NULL

Answers
   -id INTEGER PRIMARY KEY AUTOINCREMENT
   -quiz_name TEXT REFERENCES Quizzes(quiz_name)
   -prompt TEXT NOT NULL
   -value INTEGER NOT NULL

Quiz_Results
   -id INTEGER PRIMARY KEY AUTOINCREMENT
   -quiz_name TEXT REFERENCES Quizzes(quiz_name)
   -num_correct INTEGER NOT NULL
   -time TEXT NOT NULL

I want to have a table Question_Order that looks like this:

Question_Order
   -quiz_name TEXT REFERENCES Quizzes(quiz_name)
   -???some way of storing the IDs of all questions for this quiz

My first instinct was to just convert the IDs into a comma-separated string and shove it in Question_Order, but that seemed like a hacked-up solution that breaks good RDBMS practice. This question and this one asked about storing a list of IDs. But the point of having the questions in a separate table (instead of just chucking them all in the quizzes table) is that they can be reused, so I can't add an "order" column to questions. I could also create a new table for each quiz (e.g. "First_Quiz_Order") but that's also pretty messy. I'm using SQLite if it matters.

Answer 1

If you're hoping to re-use questions on different quizzes, you should not have the quiz_name field in the questions table.

You can solve that problem as well as the ordering problem by dropping quiz_name from questions and creating a table quiz_question instead of question_order:

 - quiz_name REFERENCES quizzes
 - question_id REFERENCES questions
 - question_order INT

This lets you share questions among quizzes and establish a (unique-per-quiz) order to the questions.