| |
| |
| Introduction | |
| |
| |
| |
| Names and Data Elements | |
| |
| |
| |
| Names | |
| |
| |
| |
| Watch the Length of Names | |
| |
| |
| |
| Avoid All Special Characters in Names | |
| |
| |
| |
| Avoid Quoted Identifiers | |
| |
| |
| |
| Enforce Capitalization Rules to Avoid Case-Sensitivity Problems | |
| |
| |
| |
| Follow the ISO-11179 Standards Naming Conventions | |
| |
| |
| |
| ISO-11179 for SQL | |
| |
| |
| |
| Levels of Abstraction | |
| |
| |
| |
| Avoid Descriptive Prefixes | |
| |
| |
| |
| Develop Standardized Postfixes | |
| |
| |
| |
| Table and View Names Should Be Industry Standards, Collective, Class, or Plural Nouns | |
| |
| |
| |
| Correlation Names Follow the Same Rules as Other Names... Almost | |
| |
| |
| |
| Relationship Table Names Should Be Common Descriptive Terms | |
| |
| |
| |
| Metadata Schema Access Objects Can Have Names That Include Structure Information | |
| |
| |
| |
| Problems in Naming Data Elements | |
| |
| |
| |
| Avoid Vague Names | |
| |
| |
| |
| Avoid Names That Change from Place to Place | |
| |
| |
| |
| Do Not Use Proprietary Exposed Physical Locators | |
| |
| |
| |
| Fonts, Punctuation, and Spacing | |
| |
| |
| |
| Typography and Code | |
| |
| |
| |
| Use Only Upper- and Lowercase Letters, Digits, and Underscores for Names | |
| |
| |
| |
| Lowercase Scalars Such as Column Names, Parameters, and Variables | |
| |
| |
| |
| Capitalize Schema Object Names | |
| |
| |
| |
| Uppercase the Reserved Words | |
| |
| |
| |
| Avoid the Use of CamelCase | |
| |
| |
| |
| Word Spacing | |
| |
| |
| |
| Follow Normal Punctuation Rules | |
| |
| |
| |
| Use Full Reserved Words | |
| |
| |
| |
| Avoid Proprietary Reserved Words if a Standard Keyword Is Available in Your SQL Product | |
| |
| |
| |
| Avoid Proprietary Statements if a Standard Statement Is Available | |
| |
| |
| |
| Rivers and Vertical Spacing | |
| |
| |
| |
| Indentation | |
| |
| |
| |
| Use Line Spacing to Group Statements | |
| |
| |
| |
| Data Declaration Language | |
| |
| |
| |
| Put the Default in the Right Place | |
| |
| |
| |
| The Default Value Should Be the Same Data Type as the Column | |
| |
| |
| |
| Do Not Use Proprietary Data Types | |
| |
| |
| |
| Place the PRIMARY KEY Declaration at the Start of the CREATE TABLE Statement | |
| |
| |
| |
| Order the Columns in a Logical Sequence and Cluster Them in Logical Groups | |
| |
| |
| |
| Indent Referential Constraints and Actions under the Data Type | |
| |
| |
| |
| Give Constraints Names in the Production Code | |
| |
| |
| |
| Put CHECK() Constraint Near what they Check | |
| |
| |
| |
| Consider Range Constraints for Numeric Values | |
| |
| |
| |
| Consider LIKE and SIMILAR TO Constraints for Character Values | |
| |
| |
| |
| Remember That Temporal Values Have Duration | |
| |
| |
| |
| REAL and FLOAT Data Types Should Be Avoided | |
| |
| |
| |
| Put Multiple Column Constraints as Near to Both Columns as Possible | |
| |
| |
| |
| Put Table-Level CHECK() Constraints at the End of the Table Declaration | |
| |
| |
| |
| Use CREATE ASSERTION for Multi-table Constraints | |
| |
| |
| |
| Keep CHECK() Constraints Single Purposed | |
| |
| |
| |
| Every Table Must Have a Key to Be a Table | |
| |
| |
| |
| Auto-Numbers Are Not Relational Keys | |
| |
| |
| |
| Files Are Not Tables | |
| |
| |
| |
| Look for the Properties of a Good Key | |
| |
| |
| |
| Do Not Split Attributes | |
| |
| |
| |
| Split into Tables | |
| |
| |
| |
| Split into Columns | |
| |
| |
| |
| Split into Rows | |
| |
| |
| |
| Do Not Use Object-Oriented Design for an RDBMS | |
| |
| |
| |
| A Table Is Not an Object Instance | |
| |
| |
| |
| Do Not Use EAV Design for an RDBMS | |
| |
| |
| |
| Scales and Measurements | |
| |
| |
| |
| Measurement Theory | |
| |
| |
| |
| Range and Granularity | |
| |
| |
| |
| Range | |
| |
| |
| |
| Granularity, Accuracy, and Precision | |
| |
| |
| |
| Types of Scales | |
| |
| |
| |
| Nominal Scales | |
| |
| |
| |
| Categorical Scales | |
| |
| |
| |
| Absolute Scales | |
| |
| |
| |
| Ordinal Scales | |
| |
| |
| |
| Rank Scales | |
| |
| |
| |
| Interval Scales | |
| |
| |
| |
| Ratio Scales | |
| |
| |
| |
| Using Scales | |
| |
| |
| |
| Scale Conversion | |
| |
| |
| |
| Derived Units | |
| |
| |
| |
| Punctuation and Standard Units | |
| |
| |
| |
| General Guidelines for Using Scales in a Database | |
| |
| |
| |
| Data Encoding Schemes | |
| |
| |
| |
| Bad Encoding Schemes | |
| |
| |
| |
| Encoding Scheme Types | |
| |
| |
| |
| Enumeration Encoding | |
| |
| |
| |
| Measurement Encoding | |
| |
| |
| |
| Abbreviation Encoding | |
| |
| |
| |
| Algorithmic Encoding | |
| |
| |
| |
| Hierarchical Encoding Schemes | |
| |
| |
| |
| Vector Encoding | |
| |
| |
| |
| Concatenation Encoding | |
| |
| |
| |
| General Guidelines for Designing Encoding Schemes | |
| |
| |
| |
| Existing Encoding Standards | |
| |
| |
| |
| Allow for Expansion | |
| |
| |
| |
| Use Explicit Missing Values to Avoid NULLs | |
| |
| |
| |
| Translate Codes for the End User | |
| |
| |
| |
| Keep the Codes in the Database | |
| |
| |
| |
| Multiple Character Sets | |
| |
| |
| |
| Coding Choices | |
| |
| |
| |
| Pick Standard Constructions over Proprietary Constructions | |
| |
| |
| |
| Use Standard OUTER JOIN Syntax | |
| |
| |
| |
| Infixed INNER JOIN and CROSS JOIN Syntax Is Optional, but Nice | |
| |
| |
| |
| Use ISO Temporal Syntax | |
| |
| |
| |
| Use Standard and Portable Functions | |
| |
| |
| |
| Pick Compact Constructions over Longer Equivalents | |
| |
| |
| |
| Avoid Extra Parentheses | |
| |
| |
| |
| Use CASE Family Expressions | |
| |
| |
| |
| Avoid Redundant Expressions | |
| |
| |
| |
| Seek a Compact Form | |
| |
| |
| |
| Use Comments | |
| |
| |
| |
| Stored Procedures | |
| |
| |
| |
| Control Statement Comments | |
| |
| |
| |
| Comments on Clause | |
| |
| |
| |
| Avoid Optimizer Hints | |
| |
| |
| |
| Avoid Triggers in Favor of DRI Actions | |
| |
| |
| |
| Use SQL Stored Procedures | |
| |
| |
| |
| Avoid User-Defined Functions and Extensions inside the Database | |
| |
| |
| |
| Multiple Language Problems | |
| |
| |
| |
| Portability Problems | |
| |
| |
| |
| Optimization Problems | |
| |
| |
| |
| Avoid Excessive Secondary Indexes | |
| |
| |
| |
| Avoid Correlated Subqueries | |
| |
| |
| |
| Avoid UNIONs | |
| |
| |
| |
| Testing SQL | |
| |
| |
| |
| Test All Possible Combinations of NULLs | |
| |
| |
| |
| Inspect and Test All CHECK() Constraints | |
| |
| |
| |
| Beware of Character Columns | |
| |
| |
| |
| Test for Size | |
| |
| |
| |
| How to Use VIEWS | |
| |
| |
| |
| VIEW Naming Conventions Are the Same as Tables | |
| |
| |
| |
| Always Specify Column Names | |
| |
| |
| |
| VIEWs Provide Row- and Column-Level Security | |
| |
| |
| |
| VIEWs Ensure Efficient Access Paths | |
| |
| |
| |
| VIEWs Mask Complexity from the User | |
| |
| |
| |
| VIEWs Ensure Proper Data Derivation | |
| |
| |
| |
| VIEWs Rename Tables and/or Columns | |
| |
| |
| |
| VIEWs Enforce Complicated Integrity Constraints | |
| |
| |
| |
| Updatable VIEWs | |
| |
| |
| |
| WITH CHECK OPTION clause | |
| |
| |
| |
| INSTEAD OF Triggers | |
| |
| |
| |
| Have a Reason for Each VIEW | |
| |
| |
| |
| Avoid VIEW Proliferation | |
| |
| |
| |
| Synchronize VIEWs with Base Tables | |
| |
| |
| |
| Improper Use of VIEWs | |
| |
| |
| |
| VIEWs for Domain Support | |
| |
| |
| |
| Single-Solution VIEWs | |
| |
| |
| |
| Do Not Create One VIEW Per Base Table | |
| |
| |
| |
| Learn about Materialized VIEWs | |
| |
| |
| |
| How to Write Stored Procedures | |
| |
| |
| |
| Most SQL 4GLs Are Not for Applications | |
| |
| |
| |
| Basic Software Engineering | |
| |
| |
| |
| Cohesion | |
| |
| |
| |
| Coupling | |
| |
| |
| |
| Use Classic Structured Programming | |
| |
| |
| |
| Cyclomatic Complexity | |
| |
| |
| |
| Avoid Portability Problems | |
| |
| |
| |
| Avoid Creating Temporary Tables | |
| |
| |
| |
| Avoid Using Cursors | |
| |
| |
| |
| Prefer Set-Oriented Constructs to Procedural Code | |
| |
| |
| |
| Scalar versus Structured Parameters | |
| |
| |
| |
| Avoid Dynamic SQL | |
| |
| |
| |
| Performance | |
| |
| |
| |
| SQL Injection | |
| |
| |
| |
| Heuristics | |
| |
| |
| |
| Put the Specification into a Clear Statement | |
| |
| |
| |
| Add the Words "Set of All..." in Front of Nouns | |
| |
| |
| |
| Remove Active Verbs from the Problem Statement | |
| |
| |
| |
| You Can Still Use Stubs | |
| |
| |
| |
| Do Not Worry about Displaying the Data | |
| |
| |
| |
| Your First Attempts Need Special Handling | |
| |
| |
| |
| Do Not Be Afraid to Throw Away Your First Attempts at DDL | |
| |
| |
| |
| Save Your First Attempts at DML | |
| |
| |
| |
| Do Not Think with Boxes and Arrows | |
| |
| |
| |
| Draw Circles and Set Diagrams | |
| |
| |
| |
| Learn Your Dialect | |
| |
| |
| |
| Imagine That Your WHERE Clause Is "Super Ameba" | |
| |
| |
| |
| Use the Newsgroups and Internet | |
| |
| |
| |
| Thinking in SQL | |
| |
| |
| |
| Bad Programming in SQL and Procedural Languages | |
| |
| |
| |
| Thinking of Columns as Fields | |
| |
| |
| |
| Thinking in Processes, Not Declarations | |
| |
| |
| |
| Thinking the Schema Should Look Like the Input Forms | |
| |
| |
| Resources | |
| |
| |
| Military Standards | |
| |
| |
| Metadata Standards | |
| |
| |
| ANSI and ISO Standards | |
| |
| |
| U.S. Government Codes | |
| |
| |
| Retail Industry | |
| |
| |
| Code Formatting and Naming Conventions | |
| |
| |
| Bibliography | |
| |
| |
| Reading Psychology | |
| |
| |
| Programming Considerations | |
| |
| |
| Index | |
| |
| |
| About the Author | |