I am trying to design my database, where i have to save some response data given from an external api as a webhook. This api will return me as well “referenceId” as an identifier, which i will use afterwards to relate with already existing record in database. So, the question is, is it fine to design databased columns based on the external api, as “referenceId” in this case? Can it have some downsides in future? If the api after some time either omit or change type, i would need to change it as well… Any other ideas? Thank you.
Depends on the size of the project but it's generally not a good idea to design your database based on the data types of an external API. This is because the data types and structure of the API response can change over time, which would require you to make changes to your database schema. Also, depending on the API, there may be data that is not relevant to your application and does not need to be saved in the database.
A better approach would be to design your database based on the needs of your application and the data that is relevant to it. You can then map the API response data to your database schema as needed. This will allow you to have more control over your database structure and reduce the impact of any changes to the API.
While it is common to design database schemas around surrogate keys, that is not always appropriate – when your entities have stable natural keys, they might be preferred.
In your scenario, your business domain is the external API. The entities in this domain seem to have a key in the form of the referenceId, so that would be a natural key from your perspective.
A concern with using keys from an external source is that the format or meaning of those keys could change with updates to the API. This could invalidate your database design, which can be difficult to rectify without surrogate keys. If you're storing natural keys for external entities, it could be sensible to express them as URIs. Let's consider URIs for the entity with referenceId=123:
- Sometimes, the API might provide a meaningful URI directly in form of a resolvable URL, e.g.
- Otherwise, consider creating your own URIs for this entity. A suitable mechanism for this is the
tagURI scheme which provides integrated versioning. E.g. we might describe the entity as
Such approaches can be particularly useful in systems that aggregate entities from multiple systems. But in most cases it will be simpler (and more efficient) to assign surrogate keys in your database instead.
The linked Wikipedia article about surrogate keys has more thorough discussion about the benefits and drawbacks of using surrogate keys.