4 ms·

My hot take: always use a materialized view or a stored procedure. Hide the actual, physical tables from the Application's account! The application doesn't nee

My hot take: always use a materialized view or a stored procedure. Hide the actual, physical tables from the Application's account! The application doesn't need to know how the data is physically stored in the database. They specify the logical view they need of the data. The DBAs create the materialized view/stored procedure that's needed to implement that logical view. Since the application is never directly accessing the underlying physical data, it can be changed to make the retrieval more efficient without affecting any of the database's users. You're also getting the experts to create the required data access for you in the fastest, most efficient way possible. We've been doing this for years now and it works great. It's alleviated so many headaches we used to have.

alfor 3y ago
But for the saves the structure is visible?
- taylodl 3y ago
  You can update underlying data via a materialized view.
  - NortySpock 3y ago
    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.
walterbell 3y ago
Interface contracts and indirection FTW. 2011, "Materialized Views" by Rada Chirkova and Jun Yang, https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf 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.
downWidOutaFite 3y ago
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.
- taylodl 3y ago
  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.
- icedchai 3y ago
  Also, many companies don't even have DBAs these days. DBA is, at best, a part time job for a senior+ engineer.
lanstin 3y ago
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).
wvenable 3y ago
"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.
- taylodl 3y ago
  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.
  - wvenable 3y ago
    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.
porridgeandrice 3y ago
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.

alfor 3y ago

But for the saves the structure is visible?

taylodl 3y ago
You can update underlying data via a materialized view.
- NortySpock 3y ago
  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.

walterbell 3y ago

Interface contracts and indirection FTW. 2011, "Materialized Views" by Rada Chirkova and Jun Yang, https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf 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.

downWidOutaFite 3y ago

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.

taylodl 3y ago
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.
icedchai 3y ago
Also, many companies don't even have DBAs these days. DBA is, at best, a part time job for a senior+ engineer.

lanstin 3y ago

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).

wvenable 3y ago

"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.

taylodl 3y ago
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.
- wvenable 3y ago
  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.

porridgeandrice 3y ago

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.