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

For all your programming needs

26 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]
6 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.

top 6 comments
sorted by: hot top controversial new old
[–] breakingcups 3 points 1 year ago (1 children)

The latter. Creating millions of dynamic tables for this use case is not what SQL databases are designed for.

If you create a foreign key relationship from the clues table (column questionID) to the question table (column ID), the database will even guard for you that each clue actually has a valid question associated with it. What's more, if you setup cascading deletes 9n that foreign key relationship, you only need to delete a question row and the clues will automatically be deleted for you. As you can see, this type of relationship is best modeled this way. There are many more reasons why you should do this, but I'm hoping this gives a beginners overview.

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

Thanks for sharing your knowledge

[–] [email protected] 2 points 1 year ago (1 children)

Definitely a clues table with an id to a question. The first idea doesn’t really make sense.

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

Thanks for the help

[–] vaseltarp 1 points 1 year ago* (last edited 1 year ago) (1 children)

You can create one table for all the clues and then do a 1 to n relationship. You create a collumn for the question ID in the clue table. So one question can have more than one clue and each clue knows to which question it belongs.

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

Thanks for helping