Useful ways of using Views

Here are a few ways I found Views to be useful for me.


Data Cleansing

If I have a column that needs to be cleaned or tested in some way. For example:

Select mycolumn, if(mycolumn is null, TRUE, FALSE) as mycolumnisnull from mytable

This example is not that amazing, but it lets you place conditions for the data. Then instead of re-writing these conditions in your application layer, you can re-use the View from the database.
Another example:

Select email, regex ['some amazing email verification/cleaning regex line'] as cleaned_emails from emails

Here again, we have some condition that placed in the database. We can now call the emails table and tell it to give us only the cleaned emails according to the defined conditions.


Decision Making

You can place some business logic in your Views. I wrote about it in my blog before.
This time I will just use an example:

Select business_loan, if (business_loan > 30000, 'Allow Discount', 'No Discount') as discount from loans

So here, you have an embedded rule that says if the loan is over 30,000, then give a discount. Otherwise, no discount.
There are advantages and disadvantages to using business rules in your database. One advantage is the visual way of verifying that your code is working. You can see next to your data if your code works or if you need to make changes.


Displaying parts of many-column tables

Very simply, if you know you only need a few columns from a table with many columns, you can just specify it in a View. It just keeps things tidier.

Create View contact_details
Select col11 as firstname, col52 as lastname, col37 phonenumber from 100columntable

I have used this (a lot) for finding tables to extract data from when I did some ETL-data warehouse work. This is an example:

Create View src_thetableiwaslookingfor
Select * from database1.tablethatiwant

This helped me a lot in the past and also helped when I worked with other developers on the same project. We just all used the same interfaces (Views).


Fashioning your data

You can use some basic text manipulation on your data to help you display it better.
For example:

Select concat(title,'. ',firstname, ' ', lastname) as fullname from contact_details.

Here, we just added the title, first name and last name into one string (example, Mr. John Smith).
If you really want to go crazy, you can do this

Create View HTML_export
Select concat('[tr] [td] ',firstname,'[/td] [td] ',lastname,'[/td] [/tr] ') as list from contact_details

So, if in the future you want to get a quick list of all the contact names in HTML format, you can just use the View.


Conclusion
Views are very useful :)
Especially if you dislike long-winded languages when you can do it in 1 line of SQL.

I plan on writing a more detailed article soon about this topic