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