Re: Database design conversion -- SQL?

All,

If this appears twice, it means that I have learned less than I need
to about RFC 2821 (even if I have learned more than I wanted to know
<grin />). Please accept my apology. I am having problems with my
e-mail client.


James,

It has been a while, and nobody else has responded to your
question, so let me chime in with my C$0.02-worth.

If I understand correctly, you have used the word 'instance' to
mean both the set of records growing from one root record and,
later, one of the files. I assume the latter meaning when you
write ...

Obviously, we can convert from the old design to the new
design using native record level access, but this requires a
complete new program to be written to accommodate every
instance.

I wonder why it would be hard to do the data conversion in one
program? Maybe not a pretty program, but qualified data
structures and eval-corr in RPG bring a certain economy of
expression. Cobol brings similar capabilities.

( Uh, oh! I remember an RPG limitation to--I think--fifty files
per program. And I have seen the compiler blow up when the
number of fields went beyond--I think--32K. That was a long time
ago, and I would expect those limits to have been increased. Can
somebody confirm? (Are you listening, Barbara?) )

Obviously, SQL can do the job. I cannot, however, imagine that
the SQL program would be very pretty, either. My first vague
imagining has a set of intermediate files with both the old and
new key columns and a select-join to populate each level.

Absent portability requirements or installation policies that
mandate the use of SQL, I suggest using whatever language is most
familiar.

All this is rambling and general. Still, it may provoke some
helpful discussion.

Cheers,
Terry,
rent-a-geek and database-bithead.


On Wed, 2008-06-25 at 08:54 -0800, James Lampert wrote:

Given the following situation:

We have a root file. The records in the root file can each own up to
99999 records in file A. The records in A can each own any number of
records in file B, so long as there are no more than 99999 B records for
any given root record. The records in B can own records in C, but again,
no more than 99999 per root record, and so on to any level of nesting,
but always with the constraint of no more than 99999 of a given level
sub-record per root record.

Each A, B, C, . . . record is keyed to the root record, and to a 5-digit
serial number within the root record; each B, C, . . . record is also
keyed to its parent's serial number (in A, the "parent serial number"
field is present, but always zero).

Now consider a new database design: each A, B, C, . . . record has a
long serial number as its physical file key, a field pointing to its
immediate parent's key, and a logical keyed to the parent and the serial
number. In the case of A, the "immediate parent" is the root record's
key. In the case of B, the "immediate parent" is the serial number of
the A record that owns it. In the case of C, it's the serial number of
the B record that owns it, and so forth as needed.

Within any given instance of this situation, the structure of the root
file remains unchanged. Likewise, within any given instance, the
structure of the meaningful data in A, B, C, . . . remains unchanged
from the old design to the new, but the old key fields are replaced with
new ones. But different instances may have different amounts of nesting
(from never getting past the A level, to theoretically going as far as
Z), and always differ in what the meaningful data is, and how it's
structured.


Obviously, we can convert from the old design to the new design using
native record level access, but this requires a complete new program to
be written to accommodate every instance.

Is there a way to do the conversion using SQL, that would avoid having
to write a different program for every instance?

--
James H. H. Lampert