Hi!

Let's say I have a questions system and the writers of questions always add at least one but maybe more clues for the question.

Would it be better design to have each question have its own table for clues, even though the vast majority of the time the questions only have 1 clue? (ie is it inefficient to create like a zillion tables for a database?) Or would it be better to have a "clues" table, where each clue stores which question ID the clue applies to? (ie are later queries linear in time based on the amount of clues in the table which would be bad?)

Thanks for your help! And I'd appreciate motivations for the answers too so I will understand better.

  • keepcarrot [she/her]
    ·
    10 months ago

    Unified clue table. It minimises repetition of data and will allow for generalisable queries later on (rather than having to rewrite queries for new questions).

    I think trad database design says you should have these tables: Questions Clues Table that only links questions and clues table

    Which also means you can reuse clues for different questions too