I have in my relational database three tables like this: Route -> RouteStore -> Store. So I can modify which stores can be part of a route every certain time (adding or removing) and I need to keep a history of these changes. Someone I know suggested formatting the data into a text format (JSON this case) and save it into a new table.
The reason I doubt so much about using the JSON strategy is that if I am going to use the history data, I will always have to do programming stuff to decode de data before I am able to use it. Since the database is relational I would not have any advantage using any kind of query. Doing it this way I am aware that saving the data would be pretty simple, but using it would not be as much.
The usage of these data is crucial, since I have to create a bunch of different kind of reports from the history data which would end up turning into a nightmare.
Is there a better way to keep the history of the route modifications? What alternatives should I considere?