What I find is that, in practice, there are always multiple levels of schema.

You have a physical layer of schema that enforces data types, keys, relationships, things like that. This is where you typically enforce things like making sure that a price is always a number, not a string or a date or a BLOB, and also where you enforce that every product should have a price.

Then there's the logical schema. At minimum, your semantics end up existing here, but oftentimes there are also other written or unwritten rules that the DBMS itself isn't enforcing. It's possible to set up constraints to ensure that a one-to-many relationship is actually a one-to-at-least-one relationship, for example, but I don't see it happen often in practice - those rules are only enforced by the application. Or you'll often see structures where one field's value is interpreted differently depending on the value of another field - essentially, creating a union type in the database. This is typically done in the logical schema, not the physical schema, because most people find it annoying to write the constraints you need to ensure that the DBMS itself is enforcing those rules.

When you have multiple applications accessing the DB directly, it's common for all of them to implement a slightly different version of the logical schema. At which point you might have a plethora of different schemas co-existing within the same database. For better or for worse.

Correction: you have a single authoritative view of the world, plus a secondary cached view of the world in a different language which probably doesn't perfectly map to the original one. Many people choose this approach, but it isn't without its own issues.

> you have a single authoritative view of the world, plus a secondary cached view of the world in a different language

Yes. OTOH, with NoSQL you have no authoritative schema; your code may allow for multiple different schemas, and your records may have multiple different schemas, and they could agree or disagree to any extent.

You can still have an authoritative schema, it just doesn't live in the DB itself. It would have to be defined in the code, generally.

I would argue that it's not authoritative. Nothing guarantees that the records in the db match that schema; the closest you get to a guarantee is your diligence to 1) run a job mutating all records every time you change your in-code schema and 2) ensure that nothing but your latest application code can write to the db.

OTOH, `\d users` in PostgreSQL is authoritative; there cannot be a row that does not conform to the fields, types, and constraints listed there.

> I would argue that it's not authoritative.

I think the word you're looking for is "enforced". Where there are multiple schemas, one can certainly be the "authority", even if it's not enforced across all data.

Whatever terminology you use, every place you loop through records doing stuff with them, you'll either have to decide what to do with oddly-shaped records or implicitly decide to let exceptions occur at that point. Whereas if PostgreSQL tells you that `user` has a `email character varying(255) NOT NULL`, you can be sure that every `user` does. The only place you need a conditional related to that is when trying to insert an invalid record.

I think you might be assuming that there's a single dev team using the model/domain to do things with the data. Often, at least in big enterprise dev, there will be other teams doing things: Batch processing teams with their own apps, Data Service teams doing their own things. And they will all doing this likely outside the standard dev-centric data access. And then, what sometimes happens is that even a second team gets asked to innovate on the same data store, which can potentially bring in yet another code-to-db layer. (Granted this is ideally not a great place to be at, but due to time, cost. etc. it does happen).

I generally agree with you, though. In my ideal world, people working on the batch, offline processing, etc. would be integrating through a centralized API...

I have only used Mongo with C# and the Mongo LINQ provider. When using C# you are using strongly typed IMongoQueryable<T> with strongly typed LINQ statements. The compiler enforces your schema.

One problem I had at jobs that used Mongo was that any other tools/systems that connected to the database had to be synchronized as well. It's not enough that this one system enforces a schema.

Classic example being that our code used visibility_score but a tool was adding a visibilityScore property to every document as part of a cron job calculation.

That comes down to organizational standards. When I worked with Mongo, all of the code was done in C# and the POCOs were in a shared internal Nuget package. There was never any harm in using older POCOs. You can add a catchall property to the POCOs that round trip any fields that it doesn’t know about.