Re: SQL and defining data structures

Mike, Darryl,

<Mike>
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.
</Mike>
<Darryl>
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.
</Darryl>

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:
<Mike>
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.
</Mike>
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.

D*B