1

I work for a company that wants an external system built outside of their core system. I can't share an SQL database, and can't store data in their database. So I am forced to maintain my own SQL data that has relationships with the internal data.

I am wondering how to handle SQL Foreign Keys and external data (example: someone else's API). Here are some potential solutions but each has its own pitfalls:

  1. Don't worry about a FK, and just trust that the ID is correct. With this approach you will like ping the external data source every time the data is needed. This could potentially be slow, and have authorization complexity.

  2. Download data regularly from external data sources, and store it in your own database, while frequently looking for data. This will preserve your Foreign keys, but risks data being out of date.

What are the pros and cons of these two alternatives, and are there any other suitable approach?

4
  • I see a requirement to keep the new system external, "relationships with internal data", and a preoccupation with Foreign Keys. What I don't see is a point for the external system to exist. What is it's job? What will it do? Please edit this post to add this information or it will likely get closed. Nov 1, 2021 at 20:06
  • @candied_orange: the OP mentions an "external system built outside of their core system", which seems like a web service of some sort. I wouldn't get too hung up on justifying this design decision for the purposes of this question. Nov 1, 2021 at 20:14
  • 1
    @GregBurghardt even if your assumption is correct, a web service is simply a delivery mechanic. And I'm not looking for justification of design decisions. I'm looking for the story that will tell us what this will be used for. That tells us things like if this external system collects it's own data, how dependent it is on the internal system, and how critical discrepancies between the systems are. If you have some way to know that from what this question provides please enlighten me. Nov 1, 2021 at 20:29
  • Hi, I took the opportunity to slightly reword your question because the way it was worded promotes opinion-based answer, which is out of scope here. I hope that it's still in the spirit of your original question.
    – Christophe
    Nov 1, 2021 at 22:01

1 Answer 1

4

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

  1. 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.
  2. 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
  3. 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.

2
  • Nice broad answer to a somewhat broad question. Nov 2, 2021 at 11:04
  • @Hans-MartinMosner Thank you! It's indeed broad question but quite common. So I hope this could be useful to a broader audience ;-)
    – Christophe
    Nov 2, 2021 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.