Utilizing a question and answer site such as this for example. Say I've
comment tables. Questions and solutions can have multiple comments.
Will it be better to:
AnswerCommenttables to map from questions/solutions to comments (each that contains the question/answer pk and comment pk)?
Or must i have only the comment table that contains 2 nullable foreign secrets to question and answer (one of these will be null since a comment can use simply to just one "item")?
It appears like (1) keeps referential integrity while (2) is much more compact. Is a preferred within the other? Should mapping tables be reserved just for many-to-many associations?
Are you going to use both comments in the same manner? If that's the case, then (2) otherwise (1)
If you are using (1), you may create a view over both tables to ensure they are appear as you.
Just in case (2), you can include a Trigger to enforce there being just one Foreign Key column being populated per row, or as @Ronnis recommended utilizing a CHECK constraint (a much better technique).
I have seen either approach completed in practice, however i prefer (1) a bit more:
I believe (1) expresses your domain a bit more clearly to a person browsing the schema - they'll see Answer and AnswerComment right alongside one another. If answer comments and question surveys are within the same table you need to drill lower in to the comment table to determine that object a comment can belong.
If you are utilizing a domain model with separation between domain objects and persistence, now you ask , moot: it does not matter whether you store them within the same table or different tables. (And they'd be separate classes only when they behave in a different way.)