|
|
Posted by rod.weir on 04/03/06 05:23
Fellow database developers,
I would like to draw on your experience with views. I have a database
that includes many views. Sometimes, views contains other views, and
those views in turn may contain views. In fact, I have some views in
my database that are a product of nested views of up to 6 levels deep!
The reason we did this was.
1. Object-oriented in nature. Makes it easy to work with them.
2. Changing an underlying view (adding new fields, removing etc),
automatically the higher up views inherit this new information. This
make maintenance very easy.
3. These nested views are only ever used for the reporting side of our
application, not for the day-to-day database use by the application.
We use Crystal Reports and Crystal is smart enough (can't believe I
just said that about Crystal) to only pull back the fields that are
being accessed by the report. In other words, Crystal will issue a
Select field1, field2, field3 from ReportingView Where .... even
though "ReportingView" contains a long list of fields.
Problems I can see.
1. Parent views generally use "Select * From childview". This means
that we have to execute a "sp_refreshview" command against all views
whenever child views are altered.
2. Parent views return a lot of information that isn't necessarily
used.
3. Makes it harder to track down exactly where the information is
coming from. You have to drill right through to the child view to see
the raw table joins etc.
Does anyone have any comments on this database design? I would love to
hear your opinions and tales from the trenches.
Best regards,
Rod.
Navigation:
[Reply to this message]
|