Oh, that doesn't happen much, because serious organizations have dozens of apps connected to a DB and no-one would be crazy enough to big-bang change them all at once to use a different schema. Adding columns and tables tho' is easy, populating new tables from existing, replacing tables with views, no problem.

I'd argue the inverse. Serious organisations shouldn't have lots of different apps reading from each other's databases, for the reason you suggest above. It makes changes difficult and the application landscape fragile. It also encourages not having well-documented interdependencies between your apps (and consequently no way of knowing what the impact of any given change to an app will have).

It's not uncommon however in large organisations with mature applications, without strict IT policies.

Complex applications that are undergoing active changes shouldn't be constrained in this manner, and in that context a SQL migration is more of an in-place upgrade of the data structures that hold the application data between different versions of the application.

Adding columns, adding tables, replacing columns with other columns and managing how the data should look in the new schema, based on the 'old' data structures.

They are not "each others" databases tho'. It is "the database", deliberately chosen as the integration point. The alternative is a horrific tangle of replication, or a vast undiscoverable landscape of tiny APIs.

That sounds horrible. No wonder you've never done a migration - it's been made impossible. Why would you not hide the database behind an API, so that you CAN migrate it, change it, etc? This is like not having a separate data layer in your application, except it's across many applications all at once.

What API do you suggest? Bear in mind it needs to provide the same results and enforce the same constraints for everything from COBOL to Java to Excel to ColdFusion to Python to R and any other future language. I mean you could write your business logic in all of them, and maintain it in all of them, but that would be insane.

These are solved problems, have been for decades.

Yes, and the way you solve it is by having an API that holds your business logic. That's how you avoid duplicating business logic in all those applications. Is your database just chock full of insane triggers and functions?

Business logic doesn't belong in a database. data does.

That API is called ODBC.

Or you can rewrite all the third-party apps your organisation uses now and in the future to use your own.

Doing large incompatible migrations is unavoidable eventually, so avoiding them doesn't solve the problem either it just postpones the problem until the entire enterprise has to move to an entirely "new system" at fantastic cost. For some reason the old system never really dies so after the multimillion dollar migration the old zombie system still supports some business functions, leading to that replication anyway.

Both solutions are unpalatable: "the database is the integration point we can't ever change" is bad and "no apps have a central ground truth database" is also painful.

I'd prefer the latter with small APIs and more duplication, than the common enterprise solution with a massive holy unchangable db directly serving apps.

Its not unchangeable. That's the point. You can easily add things without needing to do a "SQL migration".

Yes. But "adding" is a pretty small subset of changes. If you need to do even a trivial migration like the classic "merge first and last name columns into a single column" is pretty hairy if you have a dozen legacy apps that assume "customers" have "firstname".

In the end it means you give the data an elevated role in the system, and you write the code around the data. When the code would be cheaper to write or maintain with a different data representation - you can't. It's a comfortable situation for a while, but when you eventually grow out of the current system it's a world of hurt.

Like I said - it's two bad (non) solutions but I very much prefer duplication, isolation, inconsistency, a forest of small obscure APIs etc. where I can add/remove/normalize/denormalize/add caching etc - to "the data is the ground truth and the schema we can only change through addition" non-solution.

> In the end it means you give the data an elevated role in the system, and you write the code around the data.

That's how things are meant to be. The structure of a program naturally follows that of the data it manipulates.

> When the code would be cheaper to write or maintain with a different data representation - you can't.

Who says that? The real problem is that most programming languages aren't capable of integrating database schemas into the type-checking process, so when you change a schema, it's difficult to make sure applications follow through in a consistent manner. That's what makes change unnecessarily risky and painful. But it's possible to do better than that: http://impredicative.com/ur/

In such a scenario, none of the apps "own" the data - the DB is a primary source of truth and consistency is enforced there instead of relying that the code in the many apps will match.

Application logic can undergo active changes and refactoring without changes to the data permanent storage, indeed, a 1-to-1 correspondence between DB and application data structures isn't expected. It's just as with backwards compatibility with file formats, changing the application version shouldn't require incompatible in-place changes to the backing data persistence layer - you can need to add some new features, but the old version of the app should work fine with the updated DB.