0

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

5
  • 2
    Have you measured the performance of the two approaches? And considered the additional development headaches maintaining all that redundant, copied data will entail including fixing the inevitable bugs when they get out of synch? – 1201ProgramAlarm Oct 28 '20 at 0:43
  • 1
    I'm not sure I understand "drop one join query" - unless you're suggesting that the six boldfaced queries you've listed are currently being implemented as a six separate database requests with corresponding round-trips? If that's the case: keep working with the current schema until you get your request down to one actual query. You can have more than one two-table join in a single query! You should be able to join all those tables on the appropriate fields with the appropriate conditions and then just let the database do its work with one round-trip. – davidbak Oct 28 '20 at 0:58
  • 2
    To add to my previous comment: With SQL databases you do best to think in terms of sets of data - union, intersect, project - and then you get the best results! (You can google for tips on how to do this.) – davidbak Oct 28 '20 at 0:59
  • @1201ProgramAlarm I considered this because, once set, this data should never change. The code that sets this values exists only in one place also. – Aridez Oct 28 '20 at 3:44
  • @davidbak I misworded that. It's just dropping one join from the current query. There are other benefits coming from the framework I'm using that would end up making the code more readable, hopefully this clears things up a bit more. – Aridez Oct 28 '20 at 3:44
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.