Database Design – Evaluating the Use of Views for Each Table

databasedatabase-designsql server

We are building a Windows Mobile based application to interface with a customer's existing web application. We are reading and writing data to the customer's SQL server 2008 database.

The customer wants all reads to happen through SQL views, and all writes to happen through stored procedures.

But taking a look at their database schema, they have a view of each and every table. And they have a view of each and every query that would be used to read data, instead of using the existing views on their tables. They have 348 tables and 655 views so far.

They want us to model our mobile application in a similar fashion i.e. create additional views for all queries that we use to retrieve data.

I know this is poor design, but I am unable to list out to the customer why their view based strategy is a poor design.

What is the consensus in the community on when to use views? How does having so many views affect performance of the SQL server?

Best Answer

Its actually a pretty good design. Using views confers the following advantages:-

  • It insulates your application from any changes in the underlying physical tables.
  • A view can be regarded as the formal interface specification between client and server.
  • The view allows fine grained access control to the underlying data. I.E. it is possible give different access levels to mobile, lan based and web based clients. Clients need only have access the specific data they need rather than the entire database.
  • Views are logical constructs. There is no performance penalty in using a view.
  • In many databases views will boost performance, as query cache hit rates are usually better with views than hand coded queries.
Related Topic