Question answered by teachers and students - Database design

Question

I'm designing a database. I have Question, Answer,Student and Teacher entities Question asked only by students, but answered by teachers and students, as below:

So an Answer should have only one teacher id or Student id How can I represent this in class diagram without using inheritance ?

Answer 1

The requirements of having Questions asked by Student and related Answer provided by either Student or Teacher could be represented in UML with the help of a constraint:

So you have in your model 2 possible association for an answer in regard of the answerer: Student and Teacher. But the constraint tells that for a given answer, only one of the tow is active. Constraints are expressed between curly brackets. You can use either natural language (or pseudo-natural language like I did here), or using a more formal and less ambiguous OCL expression.

In a relational database, you could use exactly this scheme, and have for two nullable foreign keys, for example ByStudent and ByTeacher and your code would have to make sure that the constraint is respected.

An alternative way to represent this in UML, would be to show with the help of generalization the missing concept:

This is conceptually more promising. In relational databases there is however no way to express this directly. So you'd need to use some table mapping that would end up to be very similar to what is described above, or with an additional single inheritance table for mapping the Author.