Joe Celko s SQL for Smarties - Advanced SQL Programming P53. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 492 CHAPTER 22 AUXILIARY TABLES CHECK CASE WHEN code_type DDC AND code_value SIMILAR TO 0-9 0-9 0-9 . 0-9 0-9 09 THEN 1 WHEN code_type ICD AND code_value SIMILAR TO 0-9 0-9 0-9 . 0-9 0-9 09 THEN 1 WHEN code_type ISO3166 AND code_value SIMILAR TO A-Z A-Z THEN 1 ELSE 0 END 1 code_description VARCHAR 255 NOT NULL PRIMARY KEY code_value code_type Since the typical application database can have dozens and dozens of codes in it you just keep extending this pattern for as long as required. Not very pretty is it That is why most OTLT programmers do not bother with it and thus destroy data integrity. The next thing you notice about this table is that the columns are pretty wide VARCHAR n or even worse that they use NVARCHAR n . The value of n is most often the largest one allowed in that particular SQL product. Since you have no idea what is going to be shoved into the table there is no way to predict and design with a safe reasonable maximum size. The size constraint has to be put into the when clause of that second check constraint between code_type and code_value. These large sizes tend to invite bad data. You give someone a VARCHAR n column and you eventually get a string with a lot of white space and a small odd character sitting at the end of it. You give someone an NVARCHAR 255 column and eventually it will get a Buddhist sutra in Chinese Unicode. I am sure of this because I load the Diamond or Heart Sutra when I get called to evaluate a database. If you make an error in the code_type or code_description among codes with the same structure it might not be detected. You can turn from Natural Sciences and Mathematics in Dewey Decimal codes into Coal Workers Pneumoconiosis in ICD and vice versa. This can be really difficult to find when one of the similarly structured schemes had unused codes in it. Auxiliary Function Tables 493 Now let s consider the problems with actually using the OTLT in the DML. It is always necessary to add the code_type as well as .
