At least one database view per database table-Is this good or bad design and why?

Question

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?

Answer 1

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.

Answer 2

While accessing the data through a view layer is a good idea, a separate view for every query is inevitably going to mean that you are going to repeat yourself quite a bit. Inevitably, there are going to be business rules that are going to be common across many views (i.e. whatever filters you need for an entity to appear on one screen will almost certainly need to be repeated on all subsequent screens). When those business rules change, you're going to have to go through and change every view that implements those business rules. If, instead, you can embed those business rules in a single view, it is much more likely that you'll only have to change the code in one place when your logic changes.

Answer 3

Many of the answers have lots of good reasons to use views, and point out that there is no extra cost to having more views.

What may be the driver for this mandate is simply that they do not want to give you any chance to break any views/sps that exist already and potentially affect other applications, and also, their existing views/sps cannot be under suspiscion for some bug your app has that might rear it's head later.

It is another form of layering and modularisation. Everything for your app goes through "your" view/sp layer. Install or uninstall your app, and your set of views/sps is installed/uninstalled etc.

Answer 4

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

I agree that this is more difficult to handle from the maintanance point of view

• Every change in the web-gui means there must be a corresponding change in the database.
• This makes developping and rollout more complicated.

But from the security point of view this is good design:

• The database views allow fine-grained permisions (i.e. which user-role can see which columns, relating data.)
• The stored procedures protect the system from sql-injections.

From the performance point of view this shouldn-t be an issue any more because modern databases (including mssql2008) allow inexes over databaseviews.