Is nested view a good database design?
Regardless of platform, the following remarks apply.
(-) Nested views:
are harder to understand and debug
e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions...
make it harder for the query optimizer to come up with the most efficient query plan
See this, this, this, and this for anecdotal evidence. Compare to this, which shows that the optimizer is often smart enough to correctly unpack nested views and select an optimal plan, but not without a compilation cost.
You can measure the performance cost by comparing the view query to an equivalent one written against the base tables.
(+) On the other hand, nested views let you:
- centralize and reuse aggregations or business rules
- abstract away your underlying structure (say, from other database developers)
I've found that they are rarely necessary.
In your example you are using nested views to centralize and reuse certain business definitions (e.g. "What is an eligible student?"). This is a valid use for nested views. If you are maintaining or tuning this database, weigh the cost of keeping them against that of removing them.
Keep: By keeping the nested views you incur the advantages and disadvantages enumerated above.
Remove: To remove the nested views:
You need to replace all occurrences of the views with their base queries.
You must remember to update all relevant queries if your definition of eligible student/teacher/school changes, as opposed to just updating the relevant view definition.
Sometimes nested views are used to prevent repeating aggregates. Let's say you have a view that counts messages and groups them by userid, you might have a view over that that counts the number of users that have > 100 messages, that kind of thing. This is most effective when the base view is an indexed view - you don't necessarily want to create yet another indexed view to represent the data with a slightly different grouping, because now you're paying for the index maintenance twice where performance is probably adequate against the original view.
If these are all just nested views where you're doing select * but changing the ordering or top, it seems this would be better encapsulated as a stored procedure with parameters (or inline table-valued functions) than a bunch of nested views. IMHO.
Later versions of SQL (2005+) seem better at optimizing the use of views. Views are best for consolidating business rules. EG: where I work we have a telecom product database. Each product is assigned to a rateplan, and that rateplan can get swapped out, and rates on the rateplan can get activated/deacitvated as rates are increased or modified.
To make it easy, we can make nested views. 1st view just joins the rateplans to their rates using whatever tables are needed, and returning any necessary data the next levels of views would need. 2nd view(s) can isolate only active rateplans and their active rates. Or, just customer rates. Or employee rates (for employee discount). Or business vs. residential customer rates. (rateplans can get complicated). The point is, the foundation view ensures our overall business logic for rateplans and rates are joined together properly in one location. The next layer of views give us more focus on specific rateplans (types, active/inactive, etc).
I agree that views can make debugging messy if you're building queries and views at the same time. But, if you're using a tried-n-trusted view, it makes debugging easier. You know that view has already been through the ringer, so you know it's most likely not causing the problem.
Issues can come up with your views, though. "what if a product is associated only to an inactive rateplan?" or "what if a rateplan only has inactive rates on it?" Well, that can get caught at the front-end level with logic that catches user errors. "Error, product is on an inactive rateplan... please correct". We can also run query audits to double check it before a billing run. (select all plans and left join to active rateplan view, only return plans that don't get an active rateplan as problems that need to get addressed).
The good thing about this is the views let you greatly condense down queries for reporting, billing, etc. You can have a customer account view, then a 2nd-level view of just active customers. Team that with a view of customer address. Team that with a view of product(s) (joined on what product(s) customer has). Team that to view of product(s) rateplan. Team that with view of product features. View, view, view, each trial-n-errored to ensure integrity. Your end query using the views is very compact.
edit:
As an example of how the view would have been better than just a flat query of tables ... we had a temp contractor come in to make some changes. They told him there were views for things, but he decided to flatten all of his queries. Billing was running things off of some of his queries. They kept getting multiple rateplans and rates on things. Turns out his queries were missing criteria to only allow rates to bill if they were between the start & end dates the rate plan was supposed to use that/those rates during. Oops. If he had used the view, it would have already taken that logic into account.
Basically, you have to weigh performance vs. sanity. Maybe you can do all kinds of fancy stuff to increase the performance of a database. But, if it means it's a nightmare for a new person to take-over / maintain, is it really worth it? Is it really worth the new guy having to play whack-a-mole having to find all the queries that need to get their logic changed (and risk him forgetting / fat-fingering them) b/c someone decided views are "bad" and didn't consolidate some core business logic into one that could get used in 100's of other queries? It's really up to your business and your IT/IS/DB team. But, I'd prefer clarity and single-source consolidation over performance.