Database views

Database Design

Lis Sulmont

Curriculum Manager

Database views

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object (Wikipedia)

Virtual table that is not part of the physical schema

Query, not data, is stored in memory
Data is aggregated from data in tables
Can be queried like a regular database table
No need to retype common queries or alter schemas

¹ https://en.wikipedia.org/wiki/View_(SQL)

Creating a view (syntax)

CREATE VIEW view_name AS

SELECT col1, col2 
FROM table_name 
WHERE condition;

Creating a view (example)

Book dimension of the snowflake schema

Goal: Return titles and authors of the science fiction genre

Creating a view (example)

CREATE VIEW scifi_books AS

SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction';

Querying a view (example)

SELECT * FROM scifi_books

| title                         | author            | genre           |
|-------------------------------|-------------------|-----------------|
| The Naked Sun                 | Isaac Asimov      | science fiction |
| The Robots of Dawn            | Isaac Asimov      | science fiction |
| The Time Machine              | H.G. Wells        | science fiction |
| The Invisible Man             | H.G. Wells        | science fiction |
| The War of the Worlds         | H.G. Wells        | science fiction |
| Wild Seed (Patternmaster, #1) | Octavia E. Butler | science fiction |
| ...                           | ...               | ...             |

Behind the scenes

SELECT * FROM scifi_books

SELECT * FROM 
(SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction');

Viewing views

(in PostgreSQL)

SELECT * FROM INFORMATION_SCHEMA.views;

Includes system views

SELECT * FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Excludes system views

Benefits of views

Doesn't take up storage
A form of access control
- Hide sensitive columns and restrict what user can see
Masks complexity of queries
- Useful for highly normalized schemas

$$ Schema of Pitchfork Reviews Database

¹ https://www.kaggle.com/nolanbconaway/pitchfork-data

Let's practice!

Database Design