There are indeed plenty of ways to deal with this situation. And there are different solutions to address different concerns:
Concern 1: maintain the relationships
Just manage in your system the ids of related data in the foreign system, without making it a foreign key in your database scheme. You'll just assume it's correct and that's it. It's direct and simple. It's anyway what you with stable id's produced by other organisations (e.g. id cards numbers, social security numbers, phone numbers, etc..).
- pro: very simple if the ids are stable
- cons: if the id is updated in the remote system, you'd need to know all the tables where the id is used to update the ids. If for performance reasons, you want to denormalise some data (have your own copy), you'd have multiple copies to update.
Manage in your system proxy tables, with internal surrogate keys that you can use in foreign keys of related tables. Behind, you'll associate every surrogate key with the id in the foreign system. This level reduces the dependency from the other system.
- pro: you have a proxy in your RDBMS with all the referential integrity you want. You may keep your own local copy of frequently used data in one place, easy to keep in sync. This proxy corresponds to your view of the linked entities in the remote system. You don't need to know the real schema used for implementing what you need.
- cons: you need to manage this additional proxy table
Replicate in your system the entities needed in the foreign system:
- pros: easy synchronisation between system possible; your system is not affected by temporary unavailability of the remote system. Similar to 1, but the ids are controlled since they would be foreign keys.
- cons: you depend on a data scheme that you do not control. You need to know a lot more than needed on your external system, and any change in the data structure need to be reflected in your system as well. It may also impose on you practices that you want to avoid: May be the remote system uses natural keys and allows to update them. You'd have to manage this kind of process as well.
Concern 2: easy access to the data
- Minimalize data: Only the ids of the foreign system are managed on your side (option 0 or 1). Whenever data of the two system needs to be combined, you'll need to access remote data and it'll be slow (much slower).
- Maximalize data: You replicate all the data that you could potentially need. The access to the other system's data is as efficient to local data. You'll however have an overhead to periodically update the data, with the risk of having out-of sync data.
- Balanced data: you could replicate frequently used and very stable data. Most of the common data (e.g. the product name when you have a product id) would then be available efficiently with a reduced risk of being out of sync. The more occasional access (e.g. product items in stock) would require to to access remote data and it would be slow.
- Balanced data with hybrid refreshing strategies (e.g. caching like approaches, or time-stamp based replication) could further increase performance and reduce out-of-sync risks, but with the cost of a higher complexity.
A different approach?
Maintaining the relationship will be needed in any case. But how data is accessed and maintained in sync could be greatly improved with the use of an event based approach:
- updates in the remote system would publish an event in an event queue
- your system could consume the events to update the replicated data without having to update larger sets at once.
Since your company is commissioning this external system, tehy could add event publishing in their requirements from the start.