Re: DB Schema suitable for searching

[Charles Wilt <charles.wilt@xxxxxxxxx>]

John,

That's a valid technique when the search options are limited...I've
used it myself.

But in this case, with so many options...I don't believe it would work well.

Charles

On Fri, Apr 30, 2010 at 12:04 PM, Voris, John <john.voris@xxxxxxxxxxxxx> wrote:
> > This means every open invoice appears in this table 4 times, once for
> each SENTTP.
> > The app uses SQL to search for possible matches.
>
> Charles,
>
> I'll also suggest an old technique used since the days of the System
> /38.  For the alpha fields, you can re-map them so each alpha field in a
> record orients to the search field.  I have used this technique for Unit
> of Measure Lookups, (LB->OZ and OZ->LB) and a Warehouse search, where
> the users could key in the name of the City or the name of the Warehouse
> Company, and the record was brought in both ways.
>
> This is a multi-format Logical over the same Physical File.  With
> logicals like this, you do need to check the format name of the record,
> but it works great because the index is a permanent index in a logical
> file.  Here's some sample LF DDS that I have handy, where we can lookup
> in Personnel using the SSN of a Retiree or SSN of the Spouse of the
> Retiree
>  R CURNTMPM3E                PFILE(CUPNTMPM)
>   PMSSN
>   PMPNAME
>   PMCNM
>   PMSSSN
>
>  K PMSSN
>  K PMPNAME
>
>  R CURNTMPM3S                PFILE(CUPNTMPM)
>   PMSSN                     RENAME(PMSSSN)
>   PMPNAME                   RENAME(PMSNAME)
>   PMCNM
>   PMSSSN                    RENAME(PMSSN)
>
>  K PMSSN
>  K PMPNAME
>
> I know it may not be a solution you are looking for, and will probably
> require an app rewrite to do it with logicals, but performance-wise, it
> cannot be beat.  There are similar ways to do this in SQL with permanent
> indices built, but I just don't have a solution of that handy today.
> And SQL has better Numeric-to-Alpha conversions than DDS.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.