Explaining when a view should be a table -- terminology?

Question

Sometimes there are seemingly obvious concepts that are difficult to explain to other dbas (more junior) or just whoever is in charge of certain data architecture.

For instance -- when should a view become a table? (and this is a loaded question).

For me, the answer is more intuition -- but that can get you in trouble when talking to other developers.

For instance if there is giant fact/ transaction table of a 50 million records, fairly large, and you need to modify the "format" of a few fields for ingestion into external software, a view seems ideal because the calculations are quick during retrieval (that is needed anyway) --- and doubling the storage of a behemoth table has a large cost.

On the other side of the coin --- maybe there is a critical reference table. It contains details of 1,000 operational headquarters with dimensional data and calculations --- only 1,000 records though.

The view itself contains complicated joins, queries, calculations that might take 30 minutes to compute/ pull the view. HOWEVER, the underlying data barely changes day to day. Maybe 1% of records are changed overnight, and that's it.

And this view is referenced maybe 20 times throughout the day, causing 20 x 30 minutes of computation time and wait time for analysts. (in my opinion calculating the exact same thing over and over is a waste of hardware)

In this case, of course the view should be a table or materialized view. "But but the storage!" the contrarian might say. But the storage is minimal; maybe 0.01% of the database.

How can you explain these concepts to lay persons?

For some reason there seems to a penny-pinching mentality when it comes to database storage in this organization --- without holding that up against the cost of CPU/ memory (which is often overburdened) not to mention actual wait time (labor/ productivity costs).

Is there an established framework or vocabulary when it comes to decisions such as "table vs. view?" ? What metrics would determine what is ultimately more "cost efficient" ?

Answer 1

There are two intractable problems here.

1. The problem of quantifying and comparing costs and benefits. Eg how do you compare the cost of additional storage against the benefit of faster query execution? What about the cost of extra complexity? Even if you could measure them, they all have different units of measure and don't convert to a common currency.

2. The problem that the costs and benefits are divided among multiple stakeholders. Each stakeholder looks at their costs and their benefits. So why should the DBAs do something to make the users life easier?

So while you can quantify the options, you can't calculate an optimal solution. And that's why typically this comes down to leadership who must decide and enforce a policy regarding how the system should be optimized.

See generally Software Product Management and Stakeholder Analysis.