Disagree on the point and reasoning about the single database.
Sounds like they experienced badly managed and badly constrained database. The described fks and relations: that's what the key constraints and other guard rails and cascades are for - so that you are able to manage a schema. That's exactly how you do it: add in new tables that reference old data.
I think the regret is actually not managing the database, and not so much about having a single database.
"database is used by everyone, it becomes cared for by no one". How about "database is used by everyone, it becomes cared for by everyone".
Can you explain?
Having a tool to detect changes and create a migration doesn’t sound bad?
In a nutshell thats how django migrations work as well, which works really well.
Incremental forward-only migrations (non-state based).
Then, for the How and When, it mostly depends of your constraints and sizes. There's no silver bullet, it's hard, it require constant thinking, it's a slow and often multi step process.
I never saw a successful fully automated one-way-of-doing process.
> How about "database is used by everyone, it becomes cared for by everyone".
So every one needs to know every use case of that database? Seems very unlikely if there are multiple teams using same DB.
FKs? Unique constraints? Not null colums? If not added at the creation of the table they will never be added - the moment DB is part of a public API you cannot do a lot of things safely.
The only moment when you want to share DB is when you really need to squeeze every last bit of performance - and even then, you want to have one owner and severly limited user accounts (with white list of accessible views and stored procedures).
The database should never ever become part of a public API.
You don’t share a DB for performance reasons (rather the opposite), you do it to ensure data integrity and consistency.
And no, not everyone needs to know every use case. But every team needs to have someone who coordinates any overlapping schema concerns with the other teams. This needs to be managed, but it’s also not rocket science.
If database is shared it is a part of an API. If it is shared between teams then it is a public API.
If DB is shared then data from different users is entered/updated through multiple transactions. So you cannot get anything better regarding consistency and integrity compared to multiple DBs and distributed TXs.
By introducing schema change coordination you will introduce enormous delays to almost any DB change. This is more realistic than everyone knowing each use case but less practical. Shared DB is an antipattern either way.
