How to query for historical data

Question

I'm trying to implement the simple idea with Spring Data JPA and MySql. I have tables:

╔════╦═════════════════════╗
║ Id ║       Question      ║
╠════╬═════════════════════╣
║  1 ║ Who are you?        ║
║  2 ║ Whats your name?    ║
║  3 ║ Where are you from? ║
╚════╩═════════════════════╝

╔════╦════════╦═════════╦════════════╗
║ Id ║ UserId ║  Answer ║ QuestionId ║
╠════╬════════╬═════════╬════════════╣
║  1 ║    1   ║ Answer1 ║     1      ║
║  2 ║    1   ║ Answer2 ║     2      ║
║  3 ║    1   ║ Answer3 ║     3      ║
║  4 ║    2   ║ Answer4 ║     1      ║
║  5 ║    2   ║ Answer5 ║     2      ║
║  6 ║    2   ║ Answer6 ║     3      ║
╚════╩════════╩═════════╩════════════╝

And then I want to get the result as User+Question+Answer. It's good with standard Spring Data queries like answersRepository.findAll(); And I get the result:

╔════════╦═════════╦═════════════════════╗
║ UserId ║  Answer ║       Question      ║
╠════════╬═════════╬═════════════════════╣
║    1   ║ Answer1 ║ Who are you?        ║
║    1   ║ Answer2 ║ Whats your name?    ║
║    1   ║ Answer3 ║ Where are you from? ║
║    2   ║ Answer4 ║ Who are you?        ║
║    2   ║ Answer5 ║ Whats your name?    ║
║    2   ║ Answer6 ║ Where are you from? ║
╚════════╩═════════╩═════════════════════╝

But how to implement this logic if the questions can be changed? Something like User1 answered the Who are you? question and after this question has been changed to Who are u? and User2 answered on the second version for example.

I need result:

╔════════╦═════════╦═════════════════════╗
║ UserId ║  Answer ║       Question      ║
╠════════╬═════════╬═════════════════════╣
║    1   ║ Answer1 ║ **Who are you?**    ║
║    1   ║ Answer2 ║ Whats your name?    ║
║    1   ║ Answer3 ║ Where are you from? ║
║    2   ║ Answer4 ║ **Who are u?**      ║
║    2   ║ Answer5 ║ Whats your name?    ║
║    2   ║ Answer6 ║ Where are you from? ║
╚════════╩═════════╩═════════════════════╝

I tried to implement this logic using Spring Data Envers (Hibernate Envers) but this is not why the audit was invented, I think. So how can I do this? Maybe I need to use something like Event Sourcing technology or something else?

Answer 1

It really depends on the semantics of changing the question and the business relevance of it.

So let us consider a couple of variants.

1. Envers is the right tool if this is really about auditing. I.e. 99.9% of your application don't care about the different variants. Queries tend to get rather complicated since you have to figure out the correct revisions while revisions of different entities are completely independent. This is why you want to use it only for very few scenarios.

2. You don't care really about all the variants of the question that existed, you just need to make sure that for an answer you have the exact question that was present when the answer was posted. In this case you should just copy the question text into an extra column of the answer table. This sounds like data duplication, but it isn't. The question table holds the current questions. The answer table holds the question at the time of answering it. When one gets updated the other one won't.

3. If you need to keep track of all the variants of a question, you should add a variant field in the question table which will be part of the primary key and instead of changing a question it gets copied with an incremented variant-value.