How to really use SQL views

Views are virtual tables that can be a great way to optimize your database experience. Not only are views good for defining a table without using extra storage, but they also accelerate data analysis and can provide your data extra security.

In this post we will explore the benefits of using views by looking at a coffee shop’s database.

Simplified view

We’ll start with a sales table with the following columns: id, time, order_name, price, coffee_bean, syrup, milk_type, extra_shot, barista.

Jenny, one of the coffee shop’s owners, wants to analyze sale trends over the time of the day. Since the extra information in this table would complicate her trend analysis, she decides to create a view:

create view sale_aggregate as
        select extract(month from time) as month, 
               extract(day from time) as day, 
               extract(hour from time) as hour, 
               order_name, 
               count(*) 
        from sales 
        group by month, day, hour, order_name

Once the view is set up, she can query it like a normal table:

select * from sale_aggregate
where month = 12 and day = 15 and hour = 8

Now Jenny can see the relevant information from the sales table a lot faster.

Faster views

Kevin manages inventory and wants to use historical sales data to project how much inventory he should order. He needs information from several different tables, including ingredients(menu, ingredient, amount) and ingredient_prices(ingredient, price, unit).

Kevin decides to create a view to get a flat list of ingredients he needs to order.

create view inventory as 
 select 
    t.week,
    t.item_type,
    sum(t.item_amount) as order_amount,
    sum(t.item_price) as price
    from (select extract(week from time) as week,
            sales.order_name,
            sales.coffee_bean as item_type,
            sum(ingredients.amount) as item_amount,
            sum(ingredients.amount) 
                * ingredient_prices.price / ingredient_prices.unit 
                as item_price
           from sales
             join ingredients 
                on sales.order_name = ingredients.menu
             join ingredient_prices 
                on sales.coffee_bean = ingredient_prices.ingredient
          where sales.coffee_bean is not null 
            and ingredients.ingredient = 'coffee'
          group by week, sales.order_name, sales.coffee_bean, 
                    ingredient_prices.price, ingredient_prices.unit
        union
         ...) t
  group by t.week, t.item_type;

But Kevin notices his view is rather slow. He wants to maintain the fast select experience a table would provide, but his view isn’t working that way. He realizes that both the ingredients and ingredient_prices tables have grown quite large, so it’s taking a long time to join on those tables.

He solves the problem by creating indices on the columns used in the join:

create index sale_order_coffee_bean 
            on sales (order_name, coffee_bean);
create index sale_order_milk_type 
            on sales (order_name, milk_type);
create index ingredient_name 
            on ingredients (menu, ingredient);
create index ingredient_prices_ingredient 
            on ingredient_prices (ingredient);

Indices are like lookup tables that, when created strategically on columns used in joins, can accelerate queries. Now his view runs much faster without taking the extra storage a table would.

Extra security

Linda, who runs marketing at the coffee shop, realizes customers often overlook add-on items for their coffee. These add-ons are valuable because they can add a lot to the shop’s revenue without the need to bring in more customers. So she decides to show how many extra shots and coconut milk substitute are being ordered on the company website as a promotion.

Since the website is public, she does not want their web visitors possibly accessing other sensitive information in the sales table. She creates a view and limits the web’s privilege to this view to read-only:

create view extra_shots as (
    select sum(extra_shot) as extra_shot_count
    from sales);
create view coconut_milk as (
    select count(*) as coconut_milk_count)
    from sales where milk_type = 'coconut');
grant select on extra_shots to web;
grant select on coconut_milk to web;

As you can see, views are awesome ways to simplify busy tables for custom uses, save time on running popular complex queries, and limit access to certain information.