Database Design - (was Query optimizer tells me to build ... -- MIDRANGE-L

Tom and everyone,

I changed the subject line, because I'd like to start a new thread to
discuss the pros and cons of when and where to build access paths (either
LF or view/indexes), and how they relate to our environment today.

I've been an
 RPG hack since the early eighties.  While I've never been on the bleeding
edge, I tend to adopt newer technologies faster than most.  Most of my time
has been spent in consulting for numerous clients or working for a software
house, so my opportunities to implement new stuff has been dictated by who
I was working for at the time.

I've done my share of application database design (though not as much
lately), but the conversation of the previous thread has made me question
all my past assumptions.

In my experience, for the most part, i've designed with these few simple
rules in mind.

1. Nearly all permanent physical files had keys, unique keys if appropriate
(whether enforced via UNIQUE, or programmatically)
2.  Because of the overhead involved in maintaining logical file access
paths...
 a. If SQL wasn't available, logical files were built mostly for inquiries
or programs that run daily or more.
 b. If SQL was available, some of the above could/would be replaced by
embedded SQL.
3. Rarely have I used record selection in a logical- for this basically
creates single purpose access paths, and in my experience if the only
program that uses it goes into disuse, the logical rarely goes with it.
4. Opnqryf was used for most batch record selection.
5. Expected performance issues can override all of the first 4 rules, based
on file size, expected usage, etc.
6. Unexpected performance issues are handled on a case by case basis.

all of the above has been true, and has work for me with varying degrees of
success for 20 years (except for the SQL stuff, which I added to the mix a
few years back).

I'm not trying to write a book here, but I would like to have a discussion
on some rules of thumb to follow.

specifically, when designing new databases, are you exclusively using dds,
sql or a combination?

those of you who work exclusively with sql, when working with legacy
systems, have you replaced logicals with views?  added views over top of
existing logicals?

This is a topic of great interest to me, and any guidance would be greatly
appreciated.

Thanks,

Rick
-------original message-----------
This subject does show a glimpse of the differences between PFs/LFs and
tables/views/indexes. I've seen that if an LF is created over a PF and both
share the same key, then the LF file description shows the PF as owning the
access path. But a SQL CREATE INDEX using the same field doesn't appear to
share the existing PF access path; it seems that a brand new one is
created. (There might be ways of getting LFs and SQL indexes to coincide
better, but I haven't learned them.)

Two obvious considerations come to mind -- double access-path maintenance
and double space for storing the access paths.

Tom Liotta