I've found views views wildly useful for defining a single definition of a complex heirarchy defined by business rules. Especially for reporting in 'pre-packag

I've found views views wildly useful for defining a single definition of a complex heirarchy defined by business rules. Especially for reporting in 'pre-packaged' software with a fixed table structure. Often in these generic CRM or ERP systems it's surprisingly hard to actually get at the root cardinally-1 data and it's 1-n categories. My strategy for these systems is to first create a view that captures the complete business' desired cardinality (which may be surprisingly complicated), and all the reporting views and stored procedures start with that view as the first table to join off of. Ideally you can implement it with left joins in such a way that allows you to use it everywhere you need any part of the heirarchy and the SQL engine will trim the parts that you don't use in each particular query that uses it. With this you get the abstraction / reusability that enhances productivity and you get more reliable & reproducible results everywhere. It's much more likely that cross checking to separate reports works when everything is starting with the same cardinality.