I am trying to come up with a design for a simple quiz database.
My goal would be to type a simple query for the database to generate 20 (or 25 or 30 or 47 ....) random questions from my selected categories.
SELECT from 'questions' LIMIT to 20 (20 random questions)
SELECT 15 questions and 1 'history' question and 1 'geography' question and 1 'music' question and 1 'astrophysics' question (15 random and 4 fixed)
SELECT 5 'music' questions and 5 'language' questions and 5 'computer' questions and 5 'art-history' questions (20 random questions in 4 different categories)
One question could have several categories attached to it:
"What was Elvis Presley's middle name?" ('Elvis', 'USA', 'Middle Names', '20th century', 'Famous Performers')
"Name the 8 planets" ('Science', 'Astronomy', 'Solar System', 'Planets', 'Milky Way')
And I would also preferably like a user system with "last_used" attached to any question to avoid presenting the same question to the same user too often.
I have startet with a rough idea. The categories_link_table is to get several categories to one question... but something is wrong, I know...