this post was submitted on 24 Aug 2023
6 points (87.5% liked)

For all your programming needs

772 readers
1 users here now

A community to discuss programming and or related topics

founded 5 years ago
MODERATORS
[email protected]
6
SQL Question (lemmygrad.ml)
submitted 1 year ago by [email protected] to c/[email protected]
11 comments fedilink hide all child comments
 

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.

you are viewing a single comment's thread
view the rest of the comments
[–] [email protected] 5 points 1 year ago (1 children)

As others have said, you want a clues table.

The clues table needs a question_id column which is obviously a foreign key linking to the id column of the questions table.

Why?

  1. You will have only have one clues table, not hundreds
  2. You can fetch all the clues for any question really easily by just retrieving all clues from the clues table with a question_id matching the id of your question
  3. Other less important stuff, but you can do funky things like automatically delete clues when the associated question is deleted from the questions table (using ON DELETE CASCADE or your dbs equivilant).
[–] [email protected] 2 points 1 year ago (1 children)

Thanks for taking the time to write this and educate me.

[–] [email protected] 1 points 1 year ago

No worries. I'm happy to help