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:
create QuestionComment, and AnswerCommenttables to map from questions/answers to comments (each containing the question/answer pk and comment pk)?
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?
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With