CODEWITHSUNDEEP
mysqlsqlruby-on-railsdatabase-designruby-on-rails-4
msanteler
msanteler

Reputation: 2183

Database design best practice – Survey Q&A

I currently have a database set-up to collect answers for questions (Rails 4.0 + MySQL). To account for what we call a "Grid Question" such as "How often do you shop at each of the following stores?" instead of having

question has_many answers
answer belongs_to question
answer belongs_to user

I went with:

question has_many data_fields
data_field has_many answers
answer belongs_to data_field
answer belongs_to user

So that each question has 1 or more data_fields to account for each type of store someone shops in.

I recently had a database consultant insist that a better way would go back to a simple answer belongs_to question and add 3 columns in the Question table to determine 1) Is it a group defining question, 2) The 'group number' and 3) The position within the group

His explanation was that it makes it simpler if I ever find myself needing to generate a report at all the questions – I shouldn't need to do a union to accomplish this. But I'm having trouble convincing myself the benefits outweigh the strains it puts on the user interface when rendering the question input, as well as generating the data for analysis.

What is the best way to set up this database? I know this is a bit subjective, but technically there should be a best practice so hopefully I'll be allowed to post this...

Upvotes: 0

Views: 412

Answers (1)

Billy Chan
Billy Chan

Reputation: 24815

Consideration:

  1. "Group" is something related to "Question". It has nothing to do with answers. You don't need to take "Group" into consideration when defining relationship between Question and Answers.

    Question has many answers. That's native. No more thoughts.

  2. Cross table queries are unavoidable. Of course you need to try to reduce that if possible, but Relational Database won't keep its name if no cross table queries.

  3. About "Group", as Mike said, if one question has only one group, it's fine to put it as a field in Question to increase speed. But, this still has drawbacks. What if an user want to subscribe several groups(like Quora), how to do it in your tightly coupled group within Question? Can you really eliminate join queries here?

Conclusion:

Pick the most conventional way and optimize later when you really hit bottleneck.

Upvotes: 1

Related Questions