Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database design: should I use a mapping table?

Using a question and answer site like this as an example. Say I have question, answer, and comment tables. Questions and answers can each have multiple comments.

Would it be best to:

  1. create QuestionComment, and AnswerCommenttables to map from questions/answers to comments (each containing the question/answer pk and comment pk)?

  2. Or should I only have the comment table containing 2 nullable foreign keys to question and answer (one of which will always be null since a comment can apply only to a single "item")?

It seems like (1) maintains referential integrity while (2) is more compact. Is one preferred over the other? Should mapping tables be reserved only for many-to-many relationships?

like image 275
jmk23 Avatar asked Mar 04 '26 14:03

jmk23


1 Answers

Will you use both comments in the same way? If so, then (2) otherwise (1)

If you use (1), you can create a view over both tables to make them appear as one.

In case (2), you can add a Trigger to enforce there being only one Foreign Key column being populated per row, or as @Ronnis suggested using a CHECK constraint (a better technique).

like image 149
Mitch Wheat Avatar answered Mar 07 '26 09:03

Mitch Wheat



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!