tailieunhanh - SQL PROGRAMMING STYLE- P26

SQL PROGRAMMING STYLE- P26:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 92 CHAPTER 5 DATA ENCODING SCHEMES General Guidelines for Designing Encoding Schemes These are general guidelines for designing encoding schemes in a database not firm hard rules. You will find exceptions to all of them. Existing Encoding Standards The use of existing standard encoding schemes is always recommended. If everyone uses the same codes data will be easy to transfer and collect uniformly. Also someone who sat down and did nothing else but work on this scheme probably did a better job than you could while trying to get a database up and running. As a rule of thumb if you don t know the industry in which you are working ask a subject-area expert. Although that sounds obvious I have worked on a media library database project where the programmers actively avoided talking to the professional librarians who were on the other side of the project. As a result recordings were keyed on GUIDs and there were no Schwann catalog numbers in the system. If you cannot find an expert then Google for standards. First check to see if ISO has a standard then check the . government and then check industry groups and organizations. Allow for Expansion Allow for expansion of the codes. The ALTER statement can create more storage when a single-character code becomes a two-character code but it will not change the spacing on the printed reports and screens. Start with at least one more decimal place or character position than you think you will need. Visual psychology makes 01 look like an encoding whereas 1 looks like a quantity. Use Explicit Missing Values to Avoid NULLs Rationale Avoid using NULLs as much as possible by putting special values in the encoding scheme instead. SQL handles NULLs differently than values and NULLs don t tell you what kind of missing value you are dealing with. All-zeros are often used for missing values and all-nines for miscellaneous values. For example the ISO gender codes are 0 Unknown 1 Male 2 Female and 9 Not .