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
this?
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
effort.
skip to main |
skip to sidebar
Move over Donald Trump and Martha Stewart, this is geek apprentice heaven!
** This blog is not Joe Celko's but it is a collection of his answers in public newsgroups
Joe Celko The SQL Apprentice
Rows are not records; fields are not columns; tables are not files!!Move over Donald Trump and Martha Stewart, this is geek apprentice heaven!
** This blog is not Joe Celko's but it is a collection of his answers in public newsgroups
Labels
- Adjacency List Model (1)
- ANSI_NULLS (1)
- Attribute Splitting (1)
- Bit (1)
- Boolean (1)
- Calendar Table (2)
- COALESCE (1)
- Constraint (1)
- Database Design (2)
- dates (2)
- Design (1)
- Duplicates (1)
- Dynamic SQL (1)
- Formatting Data (1)
- group by (2)
- Holidays (1)
- Identity (1)
- Intersect (1)
- ISO-11179 (1)
- ISO-8601 (1)
- Nested Sets Model (1)
- Null (1)
- Order (1)
- Parameter (1)
- Query (3)
- QUOTED_IDENTIFIER (1)
- Range (1)
- Records (1)
- Refential Integrity (1)
- Rows (1)
- Sets (1)
- Settings (1)
- Soundex (1)
- tables (2)
- Temp Tables (1)
- tolerance value (1)
- UDF (1)
- Union (2)
- Update (4)
- User Defined Functions (1)
- Views (1)
- WHERE Clause (1)
1 comment:
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).
Post a Comment