Re: primary key on lower(varchar)

On Jan 6, 2006, at 11:29 PM, <me(at)alternize(dot)com> <me(at)alternize(dot)com>
wrote:

> > I don't really see the use-case for what you want anyway. Why don't
>> you just require the field to be all lower case, eg with a CHECK
>> constraint?
>
> simple case: lets say the table "translated_names" contains an
> foreign key, the translated word and the language the word is in.
> obviously, "Brotaufstrich" and "brotaufstrich" must relate to the
> same record. if i'm just saving the records in lowercase (or
> uppercase) i'm loosing the proper letter case...
>
> the workaround of adding 2 word fields (word_lower, word_normal)
> and setting word_lower to primary key unfortunately wastes a lot of
> diskspace espially when the table grows large...

If I'm understanding you correctly, you have two tables that look
like this:

names
-----------
primary key
name

translated_names
------------------------
foreign key
translated word
language

if that is the case, why not simply relate both versions of word
(lower and proper case) to the same primary key?

Charley