There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients.
We use the id of the consultant in our project. So consultant id is kind of a foreign key, but from another system/database.
What happens from time to time, is that the consultant leaves the company. Sometimes a new consultant replaces this person, sometimes not.
Basically, the id that we have been using for a client becomes invalid.
If the consultant was stored in our database, we could have used foreign keys or some other internal mechanism to prevent this.
I can't imagine that we are the only project in the world with this kind of a problem.
What are the strategies to deal with this scenario? Are there any best practices?
The two systems use different databases (DB2 and Oracle). Different host. Everything is different, except that we use the client id in our project. Is there a strategy to maintain this kind of dependency?
We are DB2 and the other system is Oracle.
Our strategy is to also check the other system periodically. I am just not sure this is the most efficient way of handling this