Mike, Darryl,

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.
This can get confusing. There is an example quoted above where you can
replace a code with a value to be more user friendly. What happens then if
the code is 2A and the description is 20A.

Following the rule to have a complete view layer, you would have two views, one is showing up the short code, the other the long description. The old parts of your application would use the first, newer reports simply the second. With the view layer you could even change the internal structure of your database (one table with the short values only and a second table with short and long column, joined in the view layer - all in one table with both - one table only having the long values.

Going a little bit deeper into the discussion:
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.
The instead of triggers will bring in some workload for every triggered operation, this would slow down the read a little bit. If you would have much reads and few writes, the implementation could be changed (if needed):
Put triggers to the write operations filling additional columns with the derived information. And again the view layer would hide the internal changes from the application.

This strategy is not restricted to new applications!!! Even old applications could be redesigned to make use of it. First step would be: eliminate all record level access by replacing all I/O operations by SQL (this wouldn't be as hard as it might sound in the first moment: write a data access SRVPGM for the RLA operations using SQL and replace the read/write/setll... by calls to the corresponding modules). Second step is to isolate the physical layer by a set of one to one views. Third step is to think about redesign in your database.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.