4

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?

2
  • I did search for this on SO and Code Review but could not find anything.
    – One-One
    Apr 18, 2012 at 4:05
  • @k3b edited title as per suggestion. Thanks.
    – One-One
    Apr 18, 2012 at 7:58

4 Answers 4

7

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.
5
  • But is a view for each query better than using existing views or joining two or more views instead?
    – One-One
    Apr 18, 2012 at 8:01
  • A view costs you nothing, defining a set of views per client gives you complete access control over what that client can do, plus, you can roll out updates one client system at a time. Apr 18, 2012 at 8:05
  • "It insulates your application from any changes in the underlying physical tables" - really? Do views still work if the underlying table changes??
    – ozz
    Apr 18, 2012 at 8:38
  • 2
    You can maintain a consistent view when columns are added to the underlying table -- you just don't include them in the view, when columns are renamed -- you just "AS OLDNAME" in the view, when column formats change -- you cast/convert the column to the old format, when columns are deleted -- you can hard code a default (possibly NULL) in your view. etc. etc. etc. Apr 18, 2012 at 8:42
  • @JamesAnderson right gotcha!
    – ozz
    Apr 18, 2012 at 9:04
3

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.

1
  • This is what I was leaning towards when I posted this question. At this point the database has so many views (655). Hard for my team to track which view is being used where if at all.
    – One-One
    Apr 18, 2012 at 8:40
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.

1
  • Hmm I was so worried about the other way around that I did not think about their concern with my application breaking their existing things.
    – One-One
    Apr 18, 2012 at 8:49
1
  > 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.

2
  • 1
    "every change in the web-gui means a corresponding ...." is irrelevant. You need to change something whether views or raw tables are used. In fact using views means you can change the underlying tables without changing the gui app. Apr 18, 2012 at 5:03
  • They have a view for each table. Is it better to join the existing table views or create a new view where ever a join between two or more tables is required?
    – One-One
    Apr 18, 2012 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.