Joe Celko's SQL Programming Style (eBook)

Front cover 1
Title page 4
Copyright page 5
Table of contents 8
front matter 16
Introduction 16
1.1 Purpose of the Book 17
1.2 Acknowledgments 18
1.3 Corrections, Comments, and Future Editions 19
First chapter 20
1. Names and Data Elements 20
1.1 Names 21
1.1.1 Watch the Length of Names 21
1.1.2 Avoid All Special Characters in Names 22
1.1.3 Avoid Quoted Identifiers 23
1.1.4 Enforce Capitalization Rules to Avoid Case- Sensitivity Problems 25
1.2 Follow the ISO-11179 Standards Naming Conventions 26
1.2.1 ISO-11179 for SQL 27
1.2.2 Levels of Abstraction 28
1.2.3 Avoid Descriptive Prefixes 29
1.2.4 Develop Standardized Postfixes 31
1.2.5 Table and View Names Should Be Industry Standards, Collective, Class, or Plural Nouns 33
1.2.6 Correlation Names Follow the Same Rules as Other Names . . . Almost 34
1.2.7 Relationship Table Names Should Be Common Descriptive Terms 36
1.2.8 Metadata Schema Access Objects Can Have Names That Include Structure Information 37
1.3 Problems in Naming Data Elements 37
1.3.1 Avoid Vague Names 37
1.3.2 Avoid Names That Change from Place to Place 38
1.3.3 Do Not Use Proprietary Exposed Physical Locators 40
2. Fonts, Punctuation, and Spacing 42
2.1 Typography and Code 42
2.1.1 Use Only Upper- and Lowercase Letters, Digits, and Underscores for Names 44
2.1.2 Lowercase Scalars Such as Column Names, Parameters, and Variables 44
2.1.3 Capitalize Schema Object Names 45
2.1.4 Uppercase the Reserved Words 45
2.1.5 Avoid the Use of CamelCase 48
2.2 Word Spacing 49
2.3 Follow Normal Punctuation Rules 50
2.4 Use Full Reserved Words 52
2.5 Avoid Proprietary Reserved Words if a Standard Keyword Is Available in Your SQL Product 52
2.6 Avoid Proprietary Statements if a Standard Statement Is Available 53
2.7 Rivers and Vertical Spacing 56
2.8 Indentation 57
2.9 Use Line Spacing to Group Statements 58
3. Data Declaration Language 60
3.1 Put the Default in the Right Place 60
3.2 The Default Value Should Be the Same Data Type as the Column 61
3.3 Do Not Use Proprietary Data Types 61
3.4 Place the PRIMARY KEY Declaration at the Start of the CREATE TABLE Statement 63
3.5 Order the Columns in a Logical Sequence and Cluster Them in Logical Groups 63
3.6 Indent Referential Constraints and Actions under the Data Type 64
3.7 Give Constraints Names in the Production Code 65
3.8 Put CHECK() Constraint Near what they Check 65
3.8.1 Consider Range Constraints for Numeric Values 66
3.8.2 Consider LIKE and SIMILAR TO Constraints for Character Values 66
3.8.3 Remember That Temporal Values Have Duration 67
3.8.4 REAL and FLOAT Data Types Should Be Avoided 67
3.9 Put Multiple Column Constraints as Near to Both Columns as Possible 67
3.10 Put Table-Level CHECK() Constraints at the End of the Table Declaration 68
3.11 Use CREATE ASSERTION for Multi-table Constraints 68
3.12 Keep CHECK() Constraints Single Purposed 69
3.13 Every Table Must Have a Key to Be a Table 70
3.13.1 Auto-Numbers Are Not Relational Keys 71
3.13.2 Files Are Not Tables 72
3.13.3 Look for the Properties of a Good Key 73
3.14 Do Not Split Attributes 81
3.14.1 Split into Tables 82
3.14.2 Split into Columns 82
3.14.3 Split into Rows 84
3.15 Do Not Use Object-Oriented Design for an RDBMS 85
3.15.1 A Table Is Not an Object Instance 85
3.15.2 Do Not Use EAV Design for an RDBMS 87
4. Scales and Measurements 88
4.1 Measurement Theory 88
4.1.1 Range and Granularity 90
4.1.2 Range 91
4.1.3 Granularity, Accuracy, and Precision 91
4.2 Types of Scales 92
4.2.1 Nominal Scales 92
4.2.2 Categorical Scales 92
4.2.3 Absolute Scales 93
4.2.4 Ordinal Scales 93
4.2.5 Rank Scales 94
4.2.6 Interval Scales 95
4.2.7 Ratio Scales 95
4.3 Using Scales 96
4.4 Scale Conversion 96
4.5 Derived Units 98
4.6 Punctuation and Standard Units 99
4.7 General Guidelines for Using Scales in a Database 100
5. Data Encoding Schemes 102
5.1 Bad Encoding Schemes 103
5.2 Encoding Scheme Types 105
5.2.1 Enumeration Encoding 105
5.2.2 Measurement Encoding 106
5.2.3 Abbreviation Encoding 106
5.2.4 Algorithmic Encoding 107
5.2.5 Hierarchical Encoding Schemes 108
5.2.6 Vector Encoding 109
5.2.7 Concatenation Encoding 110
5.3 General Guidelines for Designing Encoding Schemes 111
5.3.1 Existing Encoding Standards 111
5.3.2 Allow for Expansion 111
5.3.3 Use Explicit Missing Values to Avoid NULLs 111
5.3.4 Translate Codes for the End User 112
5.3.5 Keep the Codes in the Database 115
5.4 Multiple Character Sets 116
6. Coding Choices 118
6.1 Pick Standard Constructions over Proprietary Constructions 119
6.1.1 Use Standard OUTER JOIN Syntax 120
6.1.2 Infixed INNER JOIN and CROSS JOIN Syntax Is Optional, but Nice 124
6.1.3 Use ISO Temporal Syntax 126
6.1.4 Use Standard and Portable Functions 127
6.2 Pick Compact Constructions over Longer Equivalents 128
6.2.1 Avoid Extra Parentheses 128
6.2.2 Use CASE Family Expressions 129
6.2.3 Avoid Redundant Expressions 132
6.2.4 Seek a Compact Form 133
6.3 Use Comments 137
6.3.1 Stored Procedures 138
6.3.2 Control Statement Comments 138
6.3.3 Comments on Clause 138
6.4 Avoid Optimizer Hints 139
6.5 Avoid Triggers in Favor of DRI Actions 139
6.6 Use SQL Stored Procedures 141
6.7 Avoid User-Defined Functions and Extensions inside the Database 142
6.7.1 Multiple Language Problems 143
6.7.2 Portability Problems 143
6.7.3 Optimization Problems 143
6.8 Avoid Excessive Secondary Indexes 143
6.9 Avoid Correlated Subqueries 144
6.10 Avoid UNIONs 146
6.11 Testing SQL 149
6.11.1 Test All Possible Combinations of NULLs 149
6.11.2 Inspect and Test All CHECK() Constraints 149
6.11.3 Beware of Character Columns 150
6.11.4 Test for Size 150
7. How to Use VIEWS 152
7.1 VIEW Naming Conventions Are the Same as Tables 154
7.1.1 Always Specify Column Names 155
7.2 VIEWs Provide Row- and Column-Level Security 155
7.3 VIEWs Ensure Efficient Access Paths 157
7.4 VIEWs Mask Complexity from the User 157
7.5 VIEWs Ensure Proper Data Derivation 158
7.6 VIEWs Rename Tables and/or Columns 159
7.7 VIEWs Enforce Complicated Integrity Constraints 159
7.8 Updatable VIEWs 162
7.8.1 WITH CHECK OPTION clause 162
7.8.2 INSTEAD OF Triggers 163
7.9 Have a Reason for Each VIEW 163
7.10 Avoid VIEW Proliferation 164
7.11 Synchronize VIEWs with Base Tables 164
7.12 Improper Use of VIEWs 165
7.12.1 VIEWs for Domain Support 165
7.12.2 Single-Solution VIEWs 166
7.12.3 Do Not Create One VIEW Per Base Table 167
7.13 Learn about Materialized VIEWs 168
8. How to Write Stored Procedures 170
8.1 Most SQL 4GLs Are Not for Applications 171
8.2 Basic Software Engineering 172
8.2.1 Cohesion 172
8.2.2 Coupling 174
8.3 Use Classic Structured Programming 175
8.3.1 Cyclomatic Complexity 176
8.4 Avoid Portability Problems 177
8.4.1 Avoid Creating Temporary Tables 177
8.4.2 Avoid Using Cursors 178
8.4.3 Prefer Set-Oriented Constructs to Procedural Code 180
8.5 Scalar versus Structured Parameters 186
8.6 Avoid Dynamic SQL 187
8.6.1 Performance 188
8.6.2 SQL Injection 188
9. Heuristics 190
9.1 Put the Specification into a Clear Statement 191
9.2 Add the Words "Set of All..." in Front of the Nouns 192
9.3 Remove Active Verbs from the Problem Statement 193
9.4 You Can Still Use Stubs 193
9.5 Do Not Worry about Displaying the Data 195
9.6 Your First Attempts Need Special Handling 196
9.6.1 Do Not Be Afraid to Throw Away Your First Attempts at DDL 196
9.6.2 Save Your First Attempts at DML 197
9.7 Do Not Think with Boxes and Arrows 198
9.8 Draw Circles and Set Diagrams 198
9.9 Learn Your Dialect 199
9.10 Imagine That Your WHERE Clause Is ÏSuper AmebaÓ 199
9.11 Use the Newsgroups and Internet 200
10. Thinking in SQL 202
10.1 Bad Programming in SQL and Procedural Languages 203
10.2 Thinking of Columns as Fields 208
10.3 Thinking in Processes, Not Declarations 210
10.4 Thinking the Schema Should Look Like the Input Forms 213
back matter 216
Appendix. Resources 216
Military Standards 216
Metadata Standards 216
ANSI and ISO Standards 217
U.S. Government Codes 218
Retail Industry 218
Code Formatting and Naming Conventions 219
Appendix. Bibliography 222
Reading Psychology 222
Programming Considerations 223
Index 226
About the Author 236