2) using case insensitive DB columns is a poor default and should be corrected
You shouldn't ignore case of passwords, but you shouldn't store unencrypted passwords in a database either. So, name me things you should store in a DB where not ignoring case is a good idea. I find that it is the rare exception where some search functionality doesn't ignore case (and even in many of those exceptions it is at least open to question if that choice was the best) and yet most DBs don't actually trivially support searching (especially efficiently) in the way that is the clear best practice.
One of the few places where I think it is pretty settled that case should not be ignored is in identifiers used in a programming language. The main use of this feature is to allow a type of namespacing based on case such as global variables being capitalized in some way. But I don't understand why this one edge case seems to lead to programmers being prone to proclaiming that ignoring case is a bad idea in the general case.
And I think it is a fairly rare case that a DB column contains the name of variables from a programming language where those variables span different types of scopes.
If we follow your idea for best practice and don't jump through significant hoops, then we get behaviors like:
You can't navigate to a PerlMonks' node by typing in the name unless you get the letter case exactly right.
Requiring each e-mail address to map to at most one user fails when the case of the e-mail is changed.
When entering your e-mail address to identify your user (to log in or to request a password reset), you have to enter it using identical letter case.
When customer service asks for your name to look up your account, they also have to ask you how that name is capitalized (and you have to know how they capitalized it in that particular system).
When police look for prior incidents involving a suspect, they must try different capitalizations.
When you give emergency services your address, you have to tell them how to capitalize the street name.
When you search for a product, you have to know how to properly capitalize the name of the brand you are searching for and hope that the web site capitalizes it properly as well.
Or you jump through only a few extra hoops and end up with all of the above items being shown only in ALL CAPS (or forced into only lower case).
So, yes, ignoring case should actually be the default behavior for indexing a DB column. Yes, you should have a way to override that default for the rare case like having an identifier encoded in Base64.
Or because you decide to be "anal" about letter case and think it is a good idea to hand-code redirects for alternate letter case choices such as was done with Wikipedia. And I think it is widely accepted that the handling of letter case on Wikipedia has lead to regrets.
- tye
In reply to Re^5: Mind the meta! (case, DBs)
by tye
in thread Mind the meta!
by Anonymous Monk
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |