Continuing our story with the logistics company, let’s say that you are now working with the drivers.

The manager of the drivers said that information that would be helpful for them was the run ID, the date that run was made, the distance that was ran, the driver ID and the name of the driver. You remember that the database that you’ve created has a structure as below.

So pretty soon you were able to create some code like below that will display the exact data in the exact format that it has been asked for.

When Hayley, one of the drivers asked if she could get the same data, but just for her. No problem! You quickly modified the code to look like below.

This worked well too. So everything is going well so far! Word got out, and soon you find out that the drivers are grouped into different teams. The team leader of a team named the “Blue Team” and asked if they could get the same data for just their team. You reply “Of course!“, then ask for the driver IDs of the drivers in the team and quickly create the code below.

Quick note, you could see that we’re using “IN” to define several values that could be a match. This woudl be the equivalent of using “OR” four times.

You realize that this format, with these exact data elements in it, would be frequently used. You were talking with other developers over lunch and a senior developer mentions that in this case you should look into using a VIEW.

A VIEW could be defined similar to how a SELECT statement works, utilizing JOINs, ORDER BY, and GROUP BY. But it could used as if it’s a table, without having to create a new table. Let’s quickly take a look on how it works and dig a bit more into the benefits of VIEWs.

The code above will create the view. You could see that the code after “CREATE VIEW allDrivers AS” is the same as how the SELECT statement looked like.

Now that you’ve created a VIEW, try the commands below.

SELECT * FROM allDrivers

SELECT *  FROM allDrivers  WHERE driver_id = 7 ORDER BY run_date ASC;

SELECT *  FROM allDrivers  WHERE driver_id IN (4, 6, 7, 8) ORDER BY run_date ASC;

You could already notice that as the content and format of allDriver is a popular form, you could easily access and utilize it with less code, so it’s already quite convenient. But there are additional benefits that developers could leverage by using views.

Views allow flexibility in working with the data without having to continuously creating new tables every time there is a new ask from the users on displaying the data in a different manner. Creating new tables not only takes up more storage and more computational power to maintain and process, it also makes working with the database much more complex. Views also provides encapsulation, meaning that with the proper views, applications could use just the views and you could protect the underlying tables.

For the same table, for each user or customer, you could create different views according to the specific requirements. You could change the columns, you could also do some calculations on fields, or you could change the order, etc and make it work the same as a table. You could also hide or protect specific columns if needed.

So it’s clear that MySQL views could be helpful in making customized tables, simplifying code and also protecting code. Another interesting use-case of views could be when you have to refractor tables. Let’s say you have a crucial reason to split the “run” table that we’ve been using in this scenario. Once you split the table into two tables, even if you have all the same data, just in two tables, you will have to modify ALL the code that was created so far to accommodate this change!

If the “run” table has been used for years and hundreds or thousands of lines of code has been built and multiple applications use the “run” table, this could be a massive undertaking.

In this case, you could create a view, give it the same name, “run” and use joins so that the view is the same as the previous “run” table. This way you have allowed the split while not breaking all the code made so far using the same table.

For Hayley you could create her own view.

You could see that you are creating a view with a view.

You could also create a view for the Blue Team.

Now the same results could be obtained through the simple commands below.

SELECT * FROM allDrivers;

SELECT * FROM hayleyView; 

SELECT * FROM teamBlueView;

In MySQL Workbench’s navigator you could see that you could now see the views you’ve created.

You could also use “SHOW FULL TABLES” to get a list of the tables within the database. In the output, you could see that BASE TABLEs and and VIEWs are specified.

You can watch a video of a brief walk through of this topic here.