This topic is a good way to start a flame war because most of us have
very strong feelings about the subject.
If you have a "standard" of a view that includes all of the columns,
then what happens when you add a column? If you change the view, then
you have the same effect as changing the table. If you suffix the view
with a version number, then your users always need to know what the
current version number is. Existing queries that need to use an added
column will need to be rewritten to use the new view to get to the data.
If your users have a great tendency to always do joins on a specific set
of tables, then it may be valuable to provide "default" views that
perform the joins.
Default views are also a great way to restrict user's access to the
entire underlying table. This could be necessary in an organization
where, for example, sales offices are only allowed to access their own
data or posssibly data for the sales region that includes the specific
office.
Phil Sherman
wx***@hotmail.com wrote:
Our company is now trying to make a "standard" of creating a base
view for each user table. This initiative is suggested as a good
practice by a data modeling consultant who is helping us to build DW
logical/physical model. He pointed out that the work and risk of
making a database change will be reduced by using the "base view".
Since this base view is just a selection of all user table's columns,
as DBA, I don't see any reasons for doing that, plus we are using the
same naming standard for both table and views, there is no need to use
the base view to deal with the different name conversion. On the other
hand, it brings us lots of work to maintain the heaps of views.
Lots of debates on this topic are showed on the internet several years
ago, most of them are against the "base view" implantation.
I'm not willing to create the "base view" before I get any
reasons for doing that.
Anyone's input will be much appreciated.