I've built a webapp (PHP) that contains mysql db. The app itself collects data from user inputs and visitor behaviour on the website. Since it will be too long to explain, I will give a short example of my problem:
I have a websites table, with ID and the website name of my websites. I have another table landing_pages, with the ID and names of my landing pages, and the website_ID they belongs to. I got another table called visits, where every visit on the landing pages is recorded, with the landing page ID.
I have a reports page that shows me the visits, of course can be filtered by the website ID or by landing page ID. (or both, first website, and then by its landing pages).
Until now everyhing is working perfect. The problem came when the user changed of the landing pages' name, and then on the report it shows only the new name (obviously). Since I join the landing page name by the landing page ID.
I want to be able to still join the table by ID (so the filters still work) but to show the change in the name on the exact date it happened. so before that date I would like to display the old name, and after that date I would like to display the new name.
What will be the best approach to do it?
Many thanks in advance