SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Friday, May 04, 2007

Proper Case

SQL Apprentice Question
I have a ProperCase function that works well. However, I'm constantly
adding "exceptions" into the funciton. For example, I want IPA uppercase,
but not when it's in constIPAtion. This got me to thinking that it might be
easier to add all my cases into a table, rather than constantly editing my
function. But it's not working quite the way I'd expect it to. So rather
than reinvent the wheel, I thought I'd come here and see if anyone has taken
a table approach to ProperCase. If so, can you please share how you did

Celko Answers

>> So rather than reinvent the wheel, I thought I'd come here and see if anyone has taken a table approach to ProperCase. <<

Get a copy of SQL PROGRAMMING STYLE for more than you probably want to
know.. Formatting code was one of the big issues of the day when I
was a researcher at AIRMICS (Army Institute for Research in Management
Information & Computer Sciences) in the early 1980's. A summary is:

1) For people who read languages in a Latin alphabet, the eye tends to
jump to the uppercase letters -- they start sentences and proper
nouns, so they mark "special things" in the language. This is one
reason even MS dropped "camelCase" -- it jerks the eye around,
mentally and physically.

2) Uppercase words are read as Boumas (single units recognized by
shape). That is why the keywords in a program should be uppercase;
you wan to read them as a structure in which user words are held
(compilers will not let you misspell them, unlike written English
where all uppercase is horrible)

3) lowercase letters are actually read (as opposed to eating them as a
shape) and misspelling can be quickly seen. Newspapers and books knew
this centuries ago. That is why you use them for column names,
variables and so forth. But Capitalize (or ProperCase) schema
objects, like table and view names because they are proper nouns.

The underscore is much better than either camelCase or ProperCase.
The eye can see the two parts of the the ISO-11179 names easily and
read the postfix to learn the nature of the attribute without being
jerked to the uppercase letters.

These tested rules are much easier to implement in a language with
string functions than using a big look-up table in SQL.

Good naming conventions and formatting can reduce maintenance time by
8-12% and since that is where the real cost is, it is worth the

1 comment:

Hilarion said...

I believe it's not an answer to the question. It was not about calling conventions in programming language / SQL language / variable naming / field naming / table naming. It was about a way to enforce data consistency. For example when user (not designer) enters data into company name field in some application's form, the application (or database) could enforce proper letter case rules on those names, to make "MICROSOFT" or "MicroSoft" the proper "Microsoft". Same goes with city names, people names etc. The question probably was: If I have a MS SQL Server user defined function, that returns valid lower/upper case usage in a given text (converts the text to valid one), how should I implement exceptions in it: by coding them directly into the function body, or by storing the exceptions in database table and referring to that table from the function.
My answer would be: In general use a table, but if you have something like city name field, then consider using some kind of dictionary, so user can select values from the dictionary, and only in case of wanted value not being there at all, allow entering the value by hand (and place it in the dictionary for future use). This would require some good rules, that would prevent users from reentering values into the dictionary, when they do not notice that the value is already there, and some system for data moderation, that would allow eliminating user mistakes (this could incorporate use of general ProperCase-like function).