But for the saves the structure is visible?

You can update underlying data via a materialized view.

Heck, at least with SQL Server 2017, I've been able to write updates to a thin view, and so long as the columns referenced were not ambiguous, the database would handle the underlying tables and locks required.

Interface contracts and indirection FTW.

2011, "Materialized Views" by Rada Chirkova and Jun Yang, https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf

> We cover three fundamental problems: (1) maintaining materialized views efficiently when the base tables change, (2) using materialized views effectively to improve performance and availability, and (3) selecting which views to materialize. We also point out their connections to a few other areas in database research, illustrate the benefit of cross-pollination of ideas with these areas, and identify several directions for research on materialized views.

This doesn't work because DBAs are rarely on the dev team's sprint schedule. If the DBAs are blocking them devs can and will figure out how to route around the gatekeepers. In general, keep the logic in the app not the db.

We have sprints. We also have super responsive DBAs. Keeping the logic in the app is the path to unresponsive database calls. Been there, done that. Not going back to that crap.

In all seriousness, I won't work for an organization that works the way you describe. It's a red flag and a sign of organizational issues, personality issues, and ineffective management. Don't need to waste my time at a place like that.

Also, many companies don't even have DBAs these days. DBA is, at best, a part time job for a senior+ engineer.

That's how it was at AOL. But also, in general, people who wrote C code never designed SQL stuff. We'd come up with some requirements, meet with a DBA (who at the time got paid a lot more money, I always assumed because their work was inherently dull), they'd put together stored procedures for use to call, and then do whatever on the physical table side. They did sometimes change the physical table stuff without us having to change anything (not sharding tho, that was edit a TCL file and restart when they said to restart).

"Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." -- Fred Brooks, The Mythical Man Month (1975)

> The application doesn't need to know how the data is physically stored in the database.

In all the applications that I've designed, the application and the database design are in sync. That's not say that you wouldn't use materialized views to deal with certain broad queries but I just don't see how this level of abstraction would make a big difference.

There's the physical data model, and there's the logical data model. The application(s) only deal with the logical data model. They don't need to worry about how the data is physically stored.

This allows for the forward-compatible evolution of the logical data model which may necessitate extreme changes to the physical data model to keep everything performant. The client application(s) aren't affected by all the changes.

The application usually has a logical data model in the form of objects representing the data, which, in turn stores that data to the physical model. This could be a separate middle tier layer or not. You're proposing another logical model in the database and I don't see the advantage.

These models shouldn't be significantly different from each other. How you store the data physically should be is very much how it's represented logically. And then how the UI represents the data model to the user.

If you have 2 tables -- a summary and a detail for example -- that's going to be similarly represented to the user in the UI. If you break that summary down into another table (because you need to allow, say, more addresses) you're going to bubble that right up the UI for saving and loading. For querying, you might have a materialized view that summarizes the detail data when viewing a bunch of summaries together but that's in addition to rest of the model.

I'm going to need some kind of example to understand the advantage of this.

Yeah, I like doing this too. Not _always_, but for a few things. I use it to emulate partial functions.

In a show hosting/ticket booking app for example, I never want in any case user facing search/by-id endpoints to serve a show from 2 months ago. So I create a view `select * from shows where time > now`. I can now use this as a 'table' and apply more filters and joins to this if I wish.