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?

CodePudding user response：

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.

CodePudding user response：

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).