Why is database view used?

Question

Is using "view" in db design right method or we should handle it code side? What are the advantages or disadvantages?

Answer 1

I see a couple of reasons to use views :

• Provide a simpler interface : just query the view, and not a dozen tables, doing joins and all
• Provide an interface that doesnt change (or less often) :
  • Even if you change the structure of the tables, you might be able to modify your view so it still returns the same thing
  • Which means no change is needed in your application's code : it'll still work, as it's using the view, and not directly accessing the tables
• Only provide an interface to some fields of the tables
  • No need for the users to see some data they won't use
  • Or to access some data they should not use
• With some database engines (I think MS SQL Server supports that), some type of views can have indexes
  • Which is a good thing for performances : if you have some complex query, store it as a view, and define the required indexes on that view

Answer 2

Depends. I use them sometmies, but not that often. They are VERY usefull to expsoe decoded views on the data for use by end user (tools), like reporting applications, though. This way you can provide an end user with a simplified version of often requested information hiding some technical details.

Answer 3

Some database systems do not support embedding a SELECT statement inside a FROM clause. If you are using a system that does not support this feature, you can often save the inner SELECT statement as a view, and use the view name in place of the select statement.

So it provides behavior that can be missing in some db implementations.

Answer 4

Two typical scenarios for views in our case are:

• Some columns in a table contain confidential data that should be only seen by a few people. You can create a view that excludes those columns and use that view for most users.
• You join two or more tables into a denormalised view that is practical for reporting purposes but would not make sense as a table for storage in the database.

Hope this helps.