Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
I did search for this on SO and Code Review but could not find anything. –  One-One Apr 18 '12 at 4:05
    
@k3b edited title as per suggestion. Thanks. –  One-One Apr 18 '12 at 7:58

4 Answers 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.

share|improve this answer
    
"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. –  James Anderson Apr 18 '12 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 '12 at 8:02

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.
share|improve this answer
    
But is a view for each query better than using existing views or joining two or more views instead? –  One-One Apr 18 '12 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. –  James Anderson Apr 18 '12 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?? –  jmo21 Apr 18 '12 at 8:38
1  
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. –  James Anderson Apr 18 '12 at 8:42
    
@JamesAnderson right gotcha! –  jmo21 Apr 18 '12 at 9:04

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.

share|improve this answer
    
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 '12 at 8:40

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.

share|improve this answer
    
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 '12 at 8:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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