Hello dear Hibernate Community
Currently, an application (JavaEE) is connected to an application server (JBoss EAS), this via Hibernate (ORM) with an MS-SQL database (RDBMS), which has productive data and historical data. Historical data is almost never used; but must be available. By contrast, productive data is the data that is used for everyday use. Our data is classified in two different categories. There are data that stand alone (without relationship) and there are data that have a relationship to other objects. For example (relationship: position to item) there is a shelf with different positions (shelf space), which can be occupied by items.
The shelf must be part of the productive database, as it is constantly being worked on. If an item is on the shelf, but you do not need it, you could transfer it to an archive database (which does not exist yet). However, the relation to the position on the shelf must not be lost. Otherwise, the item was archived, but you will no longer have the position to this item and thus do not know where it is stored on the shelf.
I am going to integrate a second database (slave) on the same server (archive_db) in which the historical data is outsourced. So the productive database (master) is going to be held lean and the performance is going to improve.
My idea was to keep a single connection to the masterDB and to use a Trigger which is going to manage the data. This trigger is going to shift the data to the slaveDB or not.
e.g.: If someone want to update a reccord in the database (not knowing in which database master or slave the data are held) with use of Hibernate a trigger on the databasesite is going to kick in which is going to activate a stored procedure which is going to update the right reccord in the right database.
I think that there must be people somewhere who came across the same problem or people with the knowledge to validate my solution, or maybe open my eyes for another (better) way.
Thank you for taking the time and I am looking forward