Mastering Views in MySQL

Definition & Basic Concept of Views

Every organization, no matter how small or big saves different types of data in its data storage. To better understand the concepts of views and what they really are, let us, for this purpose, assume we are saving data collected from users in relational databases, prefferably MySQL for specifics.

Imagine…

Now, imagine this, your company collects employee information/data/details such as name, age, national identifier, address etc. When creating an application that must access this data from the database, we wont need all this data. In fact data like Passport Number is a sensitive piece of information. So what do we do? It means that we will have to hide this data from being queried and displayed on our frontend. But now, the problem is we have saved this data on a table.

So, it means we could:

• Query all data and send to our api (not recommended) or,

• Query only the needed columns, right? Pretty much so.

But now, we would like to ‘hide’ the table data entirely, such that we get the same data but also hide other sesitive data. So, ideally, we need to create a layer on top of the table (not literally but you get me? A layer before you reach the actual table).

Enter the Realm of Views

A view is a virtual table that provides an abstraction layer. We say ‘virtual’ because unlike a real table, a view does not physically store any data. It just presents the data based on the actual tables. Remember our analogy of user data? Perfect. Then it would mean that we would create a view to only present the user’s name and age and leave the sensitive data (identifier) to the actual table. This means that views help us in encapsulating the underlying business logic, helping us get the data we want without understanding the data or structure of the underlying table.

Defining Views

Ideally, we can create a view from underlying table or other views. First, create an underlying tables (the actual table you want to store data in). I have attached sql file if you need to bypass the hustle of writing all this query and use my own.

Departments table – This table contains all data regarding your organization’s departments.

-- Departments table
CREATE TABLE departments (
     dept_id INT PRIMARY KEY AUTO_INCREMENT,
     dept_name VARCHAR(50) NOT NULL,
     location VARCHAR(50),
     budget DECIMAL(12,2)
);
-- Insert departments
INSERT INTO departments (dept_name, location, budget) VALUES
  ('Engineering', 'Floor 5', 1000000.00),
  ('Marketing', 'Floor 3', 750000.00),
  ('Finance', 'Floor 2', 500000.00),
  ('HR', 'Floor 1', 400000.00);

Employees Table – This one host employee data.

-- Employees table
CREATE TABLE employees (
   emp_id INT PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   email VARCHAR(100) UNIQUE,
   hire_date DATE NOT NULL,
   salary DECIMAL(10,2),
   dept_id INT,
   manager_id INT,
   CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
   CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);
-- Insert employees
INSERT INTO employees (first_name, last_name, email, hire_date, salary, dept_id, manager_id) VALUES
     ('John', 'Smith', 'john.smith@company.com', '2015-06-15', 95000.00, 1, NULL),
     ('Sarah', 'Johnson', 'sarah.johnson@company.com', '2018-03-22', 85000.00, 1, 1),
     ('Michael', 'Williams', 'michael.williams@company.com', '2019-11-05', 75000.00, 2, NULL),
     ('Emily', 'Brown', 'emily.brown@company.com', '2020-07-30', 65000.00, 2, 3),
     ('David', 'Jones', 'david.jones@company.com', '2017-09-12', 110000.00, 3, NULL),
     ('Jessica', 'Garcia', 'jessica.garcia@company.com', '2021-01-25', 90000.00, 3, 5);

Projects Table – We then store the details about the currently running projects.

-- Projects table
CREATE TABLE projects (
  project_id INT PRIMARY KEY AUTO_INCREMENT,
  project_name VARCHAR(100) NOT NULL,
  start_date DATE,
  end_date DATE,
  budget DECIMAL(12,2),
  dept_id INT,
  CONSTRAINT fk_project_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- Insert projects
INSERT INTO projects (project_name, start_date, end_date, budget, dept_id) VALUES
   ('Website Redesign', '2023-01-15', '2023-06-30', 150000.00, 1),
   ('Market Analysis', '2023-02-01', '2023-05-15', 75000.00, 2),
   ('Financial Audit', '2023-03-01', '2023-04-30', 50000.00, 3),
   ('Mobile App Development', '2023-04-01', '2023-12-31', 300000.00, 1);

Employees_Projects Table – Stores Employee data and respective project.

-- Employee projects junction table
CREATE TABLE employee_projects (
   emp_id INT,
   project_id INT,
   hours_worked DECIMAL(6,2),
   role VARCHAR(50),
   PRIMARY KEY (emp_id, project_id),
   CONSTRAINT fk_ep_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
   CONSTRAINT fk_ep_project FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
-- Assign employees to projects
INSERT INTO employee_projects (emp_id, project_id, hours_worked, role) VALUES
       (1, 1, 120.5, 'Project Lead'),
       (2, 1, 180.0, 'Developer'),
       (1, 4, 80.0, 'Architect'),
       (3, 2, 95.5, 'Analyst'),
       (4, 2, 110.0, 'Researcher'),
       (5, 3, 60.0, 'Auditor'),
       (6, 3, 75.5, 'Assistant');

We now create a simple view that presents the department summary. You see, in our tables above we have just stored data representing the various entities we need for our organization.

But now, what if we need to create a department summary that shows the department details (name, location, and id), we then want to know the number of employees, their salary and the sum salary and total budget allocated for our department. All this data is not stored in the tables, but we need it. So we create a view.

CREATE OR REPLACE VIEW v_department_summary AS
SELECT
    d.dept_id,
    d.dept_name,
    d.location,
    COUNT(e.emp_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    SUM(e.salary) AS total_salary_budget,
    d.budget AS department_budget
FROM
    departments d
        LEFT JOIN
    employees e ON d.dept_id = e.dept_id
GROUP BY
    d.dept_id, d.dept_name, d.location, d.budget;

Our resulting view becomes:

Or even better still, we can challenge ourselves and create another view that store only needed employee details, department and project name, hours they have worked in a particular project and when the project started and ended:

CREATE OR REPLACE VIEW v_employee_project_assignments AS
SELECT
    e.emp_id,
    CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
    e.email,
    d.dept_name,
    p.project_id,
    p.project_name,
    ep.role,
    ep.hours_worked,
    p.start_date,
    p.end_date,
    CASE
        WHEN p.end_date < CURDATE() THEN 'Completed'
        WHEN p.start_date > CURDATE() THEN 'Not Started'
        ELSE 'In Progress'
        END AS project_status
FROM
    employees e
        JOIN
    employee_projects ep ON e.emp_id = ep.emp_id
        JOIN
    projects p ON ep.project_id = p.project_id
        JOIN
    departments d ON e.dept_id = d.dept_id;

So, we can see that now we have views which not only abstract data from the entities we have, but also help us define more logic with these entities without creating new entities.

Imagine that now, we want to abstract salary data so that only human resources can see salary data for the employees. After all, not everyone is allowed to see this data. So, we create another view for the same.

CREATE OR REPLACE VIEW v_hr_salary_data AS
SELECT
    emp_id,
    first_name,
    last_name,
    dept_id,
    hire_date,
    FLOOR(salary/10000)*10000 AS salary_range -- Shows salary in 10k ranges for privacy
FROM
    employees
WHERE
    dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'HR')
WITH CHECK OPTION; -- Prevents inserts/updates that would make rows disappear from view

SELECT * FROM v_hr_salary_data;

After Creating the View, what next?

Now that we have created awesome views for our data, we can then proceed to use them as we so please. Just like regular tables, you can query views (SELECT) , delete, update and drop views in accordance with your business needs.

Modify Views

Querying the views

-- Get department summaries
SELECT dept_name, employee_count, department_budget, total_salary_budget
FROM v_department_summary
ORDER BY total_salary_budget DESC;

Modifying views (Alter and Drop)

-- Alter an existing view
    ALTER VIEW v_department_summary AS
    SELECT
        d.dept_id,
        d.dept_name,
        d.location,
        COUNT(e.emp_id) AS employee_count,
        AVG(e.salary) AS avg_salary,
        SUM(e.salary) AS total_salary_budget,
        d.budget AS department_budget,
        (d.budget - SUM(e.salary)) AS remaining_budget
    FROM
        departments d
            LEFT JOIN
        employees e ON d.dept_id = e.dept_id
    GROUP BY
        d.dept_id, d.dept_name, d.location, d.budget;

-- Drop a view
DROP VIEW IF EXISTS v_old_unused_view;

Troubleshooting Hack for Views

As a database/backend developer, you will need to debug or troubleshoot or even want to understand the structure of your views. To do this, we have a technique up our sleeves, that is, querying the information_schema to get more details about our views. For example:

Show all views

-- Show all views in the database
USE mysql;

SELECT * FROM information_schema.views;

SELECT
    table_name AS view_name,
    view_definition,
    is_updatable,
    check_option
FROM
    information_schema.views

Show all dependencies:

-- Get view dependencies
SELECT
    table_name,
    referenced_table_name,
    referenced_column_name
FROM
    information_schema.key_column_usage
WHERE
    table_schema = 'mysql' AND
    referenced_table_name IS NOT NULL

Benefits of Using Views

You might be asking yourself, why though should we use these views instead of just querying the actual table with the relevant columns we need. Why the entire hustle? Views provide a few benefits:

• Enhanced data security : You see, when we create a view we restrict access to the actual schema that holds all other sensitive data. Its like having a gate to your house before the front door. This is helpful because in organizations that handle large datasets, not everyone needs or has access to all data.

• Easier Querying : we can easily query the data in the view with the set joins (especially when handling data from different tables). This means as a user, I don't have to worry about how complex the underlying actual tables are, I just get what data I need instantly.

• Faster Response Times : while views do not essentially store data (remember we mentioned they are virtual), they optimize data access and retrieval from the underlying tables. This results into faster response times for our queries.

I hope you now have a better understanding of views.

In the next series, we will learn about advanced view concepts and features such as updatable views and their limitations, using CHECK OPTION clause (as seen in our queries above), read-only views, recursive views, their use cases and performance implications views in stored objects such as procedures, functions, events and triggers and migrating views between systems.