I've been coding for over 20 years now! (WOAH, do I feel old)
I've touched just about every resource imaginable under the Sun (too bad they were bought out by Oracle)
VIEWs in general, I've personally not liked. What I ended up doing was building a SQL query generator that handled all of the JOIN logic automatically, but also dynamically, so it could be re-used for different parts of the application easily. This also allowed us to update the "application" and the "view" (handled application side) in a single Git commit, rather than having a potentially inconsistent database vs application access. Everything being maintained this way helped reduce errors, and as you've described, helped prevent a ton of performance headaches. Using this dynamic SQL query generator, it was pushed to around 30 table JOINs, and could still crank out results in single to double digit milliseconds. Each page on the web app was also able to have its own minor tweeks to this virtual "VIEW" without worrying about effecting any other page!
VIEWs in general, I've personally not liked. What I ended up doing was building a SQL query generator that handled all of the JOIN logic automatically, but also dynamically, so it could be re-used for different parts of the application easily. This also allowed us to update the "application" and the "view" (handled application side) in a single Git commit, rather than having a potentially inconsistent database vs application access. Everything being maintained this way helped reduce errors, and as you've described, helped prevent a ton of performance headaches. Using this dynamic SQL query generator, it was pushed to around 30 table JOINs, and could still crank out results in single to double digit milliseconds. Each page on the web app was also able to have its own minor tweeks to this virtual "VIEW" without worrying about effecting any other page!
You can check out the PHP code here: github.com/darkain/pudl