Please bear with me if I don't quite hit the right wording for my question, or perhaps don't understand the problem correctly.

I have an application where you can take an abstract version of something, pimp this base version up in the next sate and in the final state this something will be somehow marked unique to a specific user.

What I want to know is, how do I preserve related content of involed tables so that I can always get the final user-unique state, even when some entries (marked via grey boxes) of related tables change over time.

To give that a practical example for demonstration purpose, I have chosen a very simple ER Diagram, but you can image in real there are much more tables involved:

Book Snapshots

  1. In the first state there is book_abstract. It has only the title mandatory. The title can change over time (maybe the publisher wants to add a noun marker).
  2. The next state is book_lang_version, where the book becomes available in different languages and also in different types such as paperback or hardcover. This state relies on book_abstract to obtain title and optional category. There might be a price set in the m:n table, but it's not a must.
  3. The final state book_purchase_item is the user-unique state. This state relies on book_lang_version to know which language is chosen and further on to obtain information from book_abstract. Also the price has to be set. At last, the teaser text may be different due to some special circumstances, let's say it's an adult book and the government restrictions say, that one may only display adult teaser content during some night hours ;).

And here is my question:

What is an established and common solution to preserve the involed entries, which are scattered around different realted tables and may change in the future (I have marked the entries in question with grey boxes), so that I can rebuild the purchased book at any time in the future?

I know that there are special databases which take snapshots of the operating database, but this is not an option for me. I want to have all in one database accessible programmatically at any time.

Many thanks for any help

  • 1
    The term you're looking for is "slowly changing dimensions".
    – mustaccio
    Feb 6 at 12:28
  • Thanks for the hint @mustaccio, seems that this is really kind of what I need. So time to wrap my head around that pattern and see how to integrate it with my current application.
    – Slevin
    Feb 6 at 14:30
  • Are you currently using any particular database system and if so, which one?
    – J.D.
    Feb 6 at 14:31
  • Currently I'm with MariaDB, but since it's a Spring JPA backed application I want to keep it as open as possible and not bind myself to specific DB related functionality. It's a small project for now and I'm still learning a lot database related stuff. So I'm thankful for any tips that may spare me problems later on.
    – Slevin
    Feb 6 at 14:37


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.