Once you realize an application's primary function is providing data-driven visualizations, creating new data, updating existing data, or deleting existing data - all of which are data management functions - then you realize your data model and its handling are the most important parts of your system architecture.

Once you've crossed that bridge, you see that an RDBMS delivers two important capabilities: durably persisting the data and managing the data. There are other ways to achieve these capabilities, an RDBMS just happens to be very good for many use cases. The point, though, isn't the RDMS. It's about data. You're talking about Data-Oriented Architecture and Data-Oriented Design.

But let's stick with RDBMSs, since that's what you're currently enamored with. As you're proceeding building-out your application and all your tables that are going to be supporting it, what happens if you need to change those tables for any reason, like say performance? Since your application is issuing queries against those tables it now needs to be modified and tested. Not good. Especially if, as time goes on, there are a suite of applications built around that data. Now you have to coordinate schedules across several teams, etc., etc., etc.

The mistake being made in this scenario is the application is tied directly to the physical data model. One common way people resolve this problem is using APIs. Several applications can use the same API and if the physical data model changes, just change the API implementation and you're good to go. But that's not the route you want to take. You want to work with the RDBMS. Do we have another option?

Yes! Materialized views! Materialized views comprise your logical data model and should be thought of as the application interface to the RDBMS. The materialized views manifest all the Join logic, all the foreign keys, all that stuff. All your application has to do is Select from the materialized view. For all the application knows, a single Select from the materialized view may result in half a dozen Joins behind the scenes. The application doesn't know, and it doesn't care. That logic is kept out of the application and is pushed onto the RDBMS. If the underlying physical data model has to change, then the materialized view implementation needs to be modified. The application is not affected.

Materialized views offer other advantages as well. You have a complex query that needs a DBA to tweak the query? The materialized view has the tweak. All the applications using that view have no knowledge or care. Materialized views are far more performant than an API. Need to pull a lot of data? I mean a lot? APIs are a horrible way to go. An RDBMS? Easy-peasy! Especially when using a materialized view! Materialized views are also pre-compiled by your RDBMS query compiler - so they run faster and won't blow out the cache on your query compiler. In fact, other users who may be executing ad-hoc queries against your database won't cause the performance of your queries to suddenly start degrading in performance because their compilation has been blown out of the query cache. You don't have to employ the CQRS pattern to maintain your performance!

But here's the best thing: both Oracle and PostgreSQL (those are the RDBMS engines I use and am most familiar with) allow Updates/Deletes via a materialized view! That means while the materialized view is the RDBMS' means of delivering data to applications, applications can view that logical data model as being the data model. They can do their Select, Update and Delete through the materialized view. Put your materialized views in a separate schema from the one containing your physical tables and only allow the applications to access the schema containing your materialized views. Boom! Applications can no longer directly access the physical data model!

I've been using this approach for the past 15 years on several mission-critical systems for Fortune 200 companies. It works well, and keeps you focused on what's most important - the data!