You’re probably a medium-to-large-sized company, you run a medium-to-large application. You apply all the industry best practices, using micro-/plain-old-service architecture, compartmentalise your system into nice tidy chunks. As they usually do, your DB admin doesn’t like to maintain multiple databases and so you’ve been restricted to run everything under one fairly large database.
One fine day, you decided your data is important, it needs integrity. You decided the best thing to do is to introduce foreign keys.
You may have just made a big architectural mistake
Foreign keys are symptoms of a larger problem architecturally. In an ideal world, system dependencies should be designed in a vertical sense, and message exchange should happen at the interface layer. Inevitably, what a foreign key suggests is that there is some interface relationship between various data in your lowest level structure of your various systems. Now you’ve got at least two interface points in your system and you’ve got a future scaling issue.
There are several immediate impacts of this design decision. There’s a hint that your system is not as stateless as it should be. It also means that it’ll be fairly difficult exponentially to test and deploy your service in isolation (see continuous delivery). It also makes it impossible to maintain and mould as you lose understanding of the implicit coupling, which the ‘state’ of your service can be modified by external factors.
My proposal is that each service should control and access all interactions with its database entirely and exclusively. I’d go as far as saying that having two systems share the same database on different is a big mistake. It’d be even better, if your database instance can be deployed within the same container of your application. The mental shift here is that the database, is served as part of an application instead of a separate integration piece.
In recent years, there is a fundamental shift in philosophy about how we should maintain and access our data. As we learn and adopt smaller (but more) services and smaller (but faster) delivery, we’ve come to realise in time, we need smaller, more isolated and databases (which are part of a piece of a whole of a micro-service, not as a part of a piece itself).
It is not to say foreign keys are bad. There are small sites which will never need to scale, and in themselves qualify as singular atomic pieces which benefit from constraints to maintain data integrity. But, if you’re thinking about scale, then I would advise that you try to keep each database isolated completely.
And do not use Oracle*. Yes, you database gurus can now hate me.
* if you hadn’t caught on, just a jest … 🙂
You may also be interested in reading the counter-argument: I’m a Database Developer, this is what I think about data.