16 May 2008

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

2 comments:

  1. Jonathan,

    If you end up finding yourself with tons of views, it may be easier to generate stored procedures to do the work. That way you do a simple

    call(get_cleaned_emails)

    as your query. In this manner, developer and DBAs can work in harmony, and a DBA can optimize a query and change the database structure transparently to the developer.

    Your article seems to say that you're using views to *test* your business logic, and you can then move it to the application, which I agree views are good for. I think for some permanent views, though, a stored procedure might be better (although if you find yourself putting 7 different input arguments to a stored procedure, perhaps a view would be better....)

    ReplyDelete
  2. Sheeri,

    I have been trying to explain some of the ideas I had in my past posts. This time, I wanted to try a different approach, which is to just give practical examples and let people choose for themselves.

    Stored procedure, Views or application code... you choose whatever is more comfortable for you.
    Although, I do agree with what you mentioned and that it will probably be more comfortable to do that way.

    I wrote this article in the hope that people who didn't know about these options, will be introduced to them.


    P.S. Sheeri, thank you for following and commenting on my blog since the very beginning. I really appreciate and respect your feedback
    P.P.S. also Roland, thank you for the same reason :)

    ReplyDelete