Sample Header Ad - 728x90

How to Preserve related Table Entries for a final composition?

0 votes
0 answers
46 views
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
Asked by Slevin (101 rep)
Feb 6, 2024, 11:38 AM