At 12:14 AM 11-11-02 +0000, you wrote:

>Hi Raymond, thanks for your reply.
>
>Yeah, I was aware of this workaround (I have the most-valuable GSG),
>but to me this defeats the purpose of normalisation,

Erm, normalisation is a great mechanism for getting a clean, elegant
database but there is no database theory book that rules that you should
eliminate redundancy at all cost, to the extent that you cannot use your
data! Theoretically you can normalise to 5N, but the general
recommendation is to normalise to 4N (eliminate all redundancy) and then
wind back to "3N+" (sometimes called 3.5N) by applying commonsense and
knowledge of your implementation capability to your redundancy rules.

InterBase doesn't support case-insensitive indexes. The use of an
automatically-populated (by BI and BU triggers) case-insensitive "proxy"
column (untouched by human hand) is a perfect example of where common-sense
should prevail over the religion of Redundancy and also where the
server-side features of the database help you to achieve it safely. As
long as you never allow any user or external procedure to mess around with
your proxy column, the risk associated with data redundancy will never arise.

Index the proxy column. Remove the index on the "original" column, unless
you actually need case-sensitive ordering for something else.

>and I also do
>not want purely uppercase fields.

Your user-accessible data for stuff that you want in open case should stay
as it is. Just don't let your users get access to the proxy fields AT ALL
- don't show them in the GUI.

>The BDE lookup's can handle this, so I just thought I may have not
>been doing something correctly.

There's a great performance cost to forcing a case-insensitive match on
both sides of the comparison---this is the BDE way, which is only efficient
for desktop databases where the db structures are mapped directly into
local memory.

You can do it "the BDE way" by applying the CASE INSENSITIVE attribute
without naming a proxy column, but why would you want to? IBO supports the
more efficient method that us old hands have always used for InterBase (and
other client/server DBs) with or without the BDE, for high performance in
case-insensitive searches.

Helen