-1

I have a Rails application that includes tables for surveys and (survey) questions.

  • A survey has_many questions
  • A question belongs_to a survey

The surveys are inherently teacher surveys. Now we are introducing student surveys, which are meaningfully different from teacher surveys, with different types of information that we need to store about them, such that they seem to each warrant their own table/model, so I'm thinking we want separate tables for teacher_surveys and student_surveys.

However, the questions are really pretty much the same. Includes things like the question text, type of question (text, checkbox, dropdown), etc. So it seems like questions should remain a single table.

How do I best model this data?

  • Should the questions table have a teacher_survey_id and a student_survey_id where each is optional but one of the two of them is required?
  • Should I have join tables for questions_teacher_surveys and questions_student_surveys?
  • Something else?
user3060126
  • 461
  • 1
  • 4
  • 13
  • 1
    If you are going to reuse questions in different surveys then it's not a `belongs_to` relation. For reusing questions I would use a join table of survey_questions that would have `survey_id` and `question_id`. Then it's a has_many_through relation. And it would even give you the ability to go the other direction to see what surveys a particular question is used in. – Beartech Jan 06 '23 at 00:47
  • As for two type of surveys, what are the main differences? Do they require different columns? Then a separate table is probably best. If they are very similar then you could do single table inheritance. But that's a very subjective question. – Beartech Jan 06 '23 at 00:49
  • A question will only every belong to one survey, either a teacher_survey or a student_survey. teacher_surveys and student_surveys will require mostly different columns. – user3060126 Jan 06 '23 at 01:09
  • Then I would use two different tables and has_many/belongs_to relationship. But you might be better served looking for DB design advice over at https://dba.stackexchange.com – Beartech Jan 06 '23 at 05:02
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jan 07 '23 at 06:07

2 Answers2

0

There is no easy answer to this question. Separating questions into student_question and teach_question tables does mean you have a slight bit of duplication and you can't query them as a homogenized collection if that happens to be important.

The code duplication can be very simply addressed by using inheritance or composition but there is an increased maintainence burdon / complexity cost here.

But it does come with the advantage that you get a guarentee of referential integrity from the non-nullable foreign key column without resorting to stuff like creating a database trigger or the lack of real foreign key constraints if you choose to use a polymorphic association.

An additional potential advantage is that you're querying smaller tables that can remain dense instead of sparse if the requirements diverge.

max
  • 96,212
  • 14
  • 104
  • 165
  • Interesting, thanks! How would the polymorphic association work in this case? – user3060126 Jan 06 '23 at 18:01
  • A polymorphic assocation uses an foreign key column (lets call it subject_id) together with a string column (subject_type) which contains the class name. Its a dirty hack around the object relational impedence missmatch problem. – max Jan 07 '23 at 09:09
0

sounds like you need to make your surveys table polymorphic and add a type column as an enum with :student and :teacher as options, you can use the type column as a flag to control the different business logic. Once the survey table becomes polymorphic you probably wont need to do anything with your questions table. If you decide to go with this solution its also recommend to add a concern class named Surveyable to hold the needed associations and shared logic between your surveyed models (in your case students and teachers).

Omar Luq
  • 16
  • 4