Does it make sense to have redundant DB fields to improve query performance, programming speed and code readability?

Question

I am a bit torn about the database design for a solution. I have the following structure: (only the relevant fields are marked with #)

+------------------+
|    activities    |
|------------------|
|  #connection_id  |
|  #updated_at     |
|  #activity_type  |
|  #status         |
+------------------+
          |
+---------------------+   +----------------+
| sent_questionnaires |---| questionnaires |
|---------------------|   +----------------+
|   #activity_id      |   |     #id        |
|   #questionnaire_id |   +----------------+
+---------------------+   
         |
         +---------------------+
         |                     |
+-------------------------+   +-------------------------+
|    correction_scores    |   |   correction_messages   |
+-------------------------+   +-------------------------+
| #sent_questionnaire_id  |   | #sent_questionnaire_id  |
+-------------------------+   +-------------------------+

And I have the following implementation details:

• The activities.connection_id field identifies a sender-receiver relationship.
• The activities <> sent_questionnaires relationship is polymorphic, the type is stored on the activity_type field. There are other types besides questionnaires.
• Each questionnaire correction is programmed individually based on the questionnaire type. Each correction may contain scores, messages, both or neither. There are more tables storing the correction data information and they are queried based on the questionnaire type.

Based on this I now have to gather all the information of the last 5 questionnaires of a single type and on a single connection.

Current solution

Without modifying the schema, the current solution would be to query for the following information:

• activities.activity_type: to query only for questionnaires
• activities.connection_id: to query for the questionnaires of a single sender-receiver
• activities.updated_at: to know which are the latest 5
• activities.status: to get only completed activities
• sent_questionnaires.questionnaire_id: to know which correction apply (which tables to query among other things)
• correction_scores / correction_messages: to get the correction contents

Alternative solution

Replicating the activities.connection_id, the activities.updated_at and the activities.status on the sent_questionnaires table I could drop one join query.

The advantages I see are:

• This is an operation that is performed often, this change would make it a bit faster
• Since each new questionnaire requires to have a tailor-made correction programmed, this would make the code feel easier to write and easier to read later on

The disadvantages I see are:

• It would create a couple of redundant fields used only to make queries faster/cleaner
• The data consistency would be kept at the application layer instead of the database layer

The schema would look like this:

+------------------+
|    activities    |
|------------------|
|  #connection_id  |
|  #updated_at     |
|  #activity_type  |
|  #status         |
+------------------+
          |
+---------------------+
| sent_questionnaires |
|---------------------|
|   #activity_id      |
|   #questionnaire_id |
| ###connection_id    |
| ###updated_at       |
| ###status           |
+---------------------+   

The question

Does it make sense to create this kind of data redundancy in a database in favor of code readability, query performance and new questionnaire implementation speed?

*In case it matters, I'm using MySQL

Answer 1

It can make sense if you are achieving a large performance improvement. The techniques you describe are broadly known as "materialized views". The typical scenarios where they are used are pre-aggregation and avoiding a sorting operation where you have a GROUP BY or ORDER BY clause spanning more than one table.

In order to ensure data integrity, all redundant columns (or tables) should be implemented using triggers. That will ensure that changing the primary source of truth changes all redundant data at the same time.