Re: SQL and defining data structures

Well said Dieter.

Views can also provide benefit beyond allowing applications to continue
running *unmodified* after database changes. They can make your data
appear different, in situations where you can't justify changing, or
afford to change, the database. Their benefit goes beyond insulating you
from database changes, if you consider them a tool to simulate database
changes.

Example use for making your data appear different...

Let's say you have a code table with codes 01, 02, 03, 04, 05, etc., and
there are 20 reports in your system where users prefer more meaningful
codes to be shown instead. By placing the code column value transformation
in a view, and using the view in those 20 reports, the users get what they
want and you didn't have to change the database or the application.

If your system used views like that for reading, and for INSERT / UPDATE /
DELETE operations, implemented code column value reversal transformations
inside INSTEAD OF triggers, your entire application could use a more user
friendly set of codes on the screen and on reports (i.e. entire read/write
presentation layer), and your database could continue using the less user
friendly legacy code values. The application would be unaware that the
transformations are taking place.

SQL views, when widely used in your system, allow you to provide data
transformations to the presentation layer without changing the database or
the application.

In my line of work doing database migrations, database acquisitions /
merges, and ETL type projects, I get a ton of mileage using SQL views to
perform much of the database transformation work. One view transforming a
set of data, fed off of by, for example, 10 different sections of code, is
better than 10 sections of code each redundantly performing the same
transformation. My use case tasks and implementations lend themselves very
well to that stuff, but I think there's some room for leveraging data
transformations inside SQL views in day to day, operational systems.

The amount of mileage you get out of making your data appear different
depends a lot on your imagination and creativity, the type of data
interfaces you choose to access, and the flexibility of the tools,
techniques, and languages used to interact with those interfaces.

Few would disagree that it is difficult to get the time and funding to swap
in SQL view interfaces across an old, large system without them. But, at
least for new development, if you anticipate and design for accommodating
transformations in your data layer, they can speed up and reduce the cost
of delivering those new applications and subsequent enhancements, imo.

In summary, views can reduce your work load (insulate you from DB
changes). If you design for and provide a flexible implementation that
accommodates data layer transformations, they can also speed up changes you
do make, in some cases, by eliminating the need to change the database or
the application, and instead making a change to the middle, data layer
only.

Mike

On Wed, Aug 30, 2017 at 11:42 PM, D*B <dieter.bender@xxxxxxxxxxxx> wrote:

<Joe Pluta>
My one unbreakable rule is that there are no unbreakable rules.
</Joe Pluta>

for every rule, there will be people, not understanding the rule and they
will break it, but mostly they will earn some punishment.

<Joe Pluta>
So yes, you're referencing the file, but no, you're not in danger of a
level check.
</Joe Pluta>

SQL (and we where talking about SQL) has no level checks, never ever!!!

The rule to have a complete view layer between the database and
applications decouples your application from the physical implementation

of

your database. You could make changes in your database and as long as you
could provide the same views with the same behaviour, your application
won't see it and wouldn't need any change. Database access get's very

easy,

just select * from someView where some conditions, order by some columns
and with the capability of external DS you could fetch into a suitable

DS.

Introducing another rule: avoid changing a view (instead you could add
another!!!) change impact is minimized (very important for 24/7) makes

the

life more easy again.
Some punishment you will earn following the proposal with the Dtaara
approach would be: changing your select statement, it could happen that
your ResultSet doesn't match the DS and Murphy says: shit happens (and
that's a rule, even Joe can't break), swapping two columns, the columns
could fit to the fields and you would have a happy time to find this,

maybe

weeks later.


D*B

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD