Back To Course Home

SQL Antipatterns: Avoiding the Pitfalls of Database Programming

0% completed

Introduction

Getting Started Course Structure Conventions Example Database

Logical Antipattern - Jaywalking

Synopsis: Jaywalking Antipattern: Format Comma-Separated Lists Solution: Create an Intersection Table

Logical Antipattern - Naive Trees

Synopsis: Naive Trees Antipattern: Always Depend on One’s Parent Maintaining a Tree with an Adjacency List Solution: Use Alternative Tree Models Path Enumeration Nested Sets Closure Table Comparison of Different Tree Implementations

Logical Antipattern - ID Required

Synopsis: ID Required Antipattern: One Size Fits All Solution: Tailored to Fit

Logical Antipattern - Keyless Entry

Synopsis: Keyless Entry Antipattern: Leave Out the Constraints Solution: Declare Constraints

Course Assessment

Assessment: Logical Antipatterns - Part 1

Logical Antipattern - Entity-Attribute-Value

Synopsis: Entity-Attribute-Value Antipattern: Use a Generic Attribute Table Supporting Data Integrity Solution: Model the Subtypes Using Inheritance Suboptimal Solutions

Logical Antipattern - Polymorphic Associations

Synopsis: Polymorphic Associations Antipattern: Use Dual-Purpose Foreign Key Solution 1: Simplify the Relationship Solution 2: Create a Common Super-Table

Logical Antipattern - Multicolumn Attributes

Synopsis: Multi-column Attributes Antipattern: Create Multiple Columns Solution: Create Dependent Table

Logical Antipattern - Metadata Tribbles

Synopsis: Metadata Tribbles Antipattern: Clone Tables or Columns Solution: Partition and Normalize

Course Assessment

Assessment: Logical Antipatterns - Part 2

Physical Antipattern - Rounding Errors

Synopsis: Rounding Errors

Antipattern: Use FLOAT Data Type

Solution: Use NUMERIC Data Type

Physical Antipattern - 31 Flavors

Synopsis: 31 Flavors Antipattern: Specify Values in the Column Definition Solution: Specify Values in Data

Physical Antipattern - Phantom Files

Synopsis: Phantom Files Antipattern: Assume You Must Use Files Solution: Use BLOB Data Types As Needed

Physical Antipattern - Index Shotgun

Synopsis: Index Shotgun Antipattern: Using Indexes Without a Plan Solution: MENTOR Your Indexes

Course Assessment

Assessment: Physical Antipatterns

Query Antipattern - Fear of the Unknown

Synopsis: Fear of the Unknown Antipattern: Use Null as an Ordinary Value, or Vice Versa Solution: Use NULL as a Unique Value

Query Antipattern - Ambiguous Groups

Synopsis: Ambiguous Groups Antipattern: Reference Non-grouped Columns Solution: Use Columns Unambiguously

Query Antipattern - Random Selection

Synopsis: Random Selection Antipattern: Sort Data Randomly Solution: In No Particular Order

Query Antipattern - Poor Man’s Search Engine

Synopsis: Poor Man’s Search Engine Antipattern: Pattern Matching Predicates Solution: Use the Right Tool for the Job Alternatives To Make Text Searchable

Query Antipattern - Spaghetti Query

Synopsis: Spaghetti Query Antipattern: Solve a Complex Problem in One Step Solution: Divide and Conquer

Query Antipattern - Implicit Columns

Synopsis: Implicit Columns Antipattern: A Shortcut That Gets You Lost Solution: Name Columns Explicitly

Course Assessment

Assessment: Query Antipattern

Application Development Antipattern - Readable Passwords

Synopsis: Readable Passwords Antipattern: Store Password in Plain Text Solution: Store a Salted Hash of the Password Other Ways to Secure Our Database Passwords

Application Development Antipattern - SQL Injection

Synopsis: SQL Injection Antipattern: Execute Unverified Input As Code The Quest for a Cure Solution: Trust No One

Application Development Antipattern - Pseudokey Neat-Freak

Synopsis: Pseudo Key Neat-Freak Antipattern: Filling in the Corners Solution: Strictly Follow Referencing Rules

Application Development Antipattern - See No Evil

Synopsis: See No Evil Antipattern: Making Bricks Without Straw Solution: Recover from Errors Gracefully

Application Development Antipattern - Diplomatic Immunity

Synopsis: Diplomatic Immunity Antipattern: Make SQL a Second-Class Citizen Solution: Establish a Big-Tent Culture of Quality

Application Development Antipattern - Magic Beans

Synopsis: Magic Beans Antipattern: The Model Is an Active Record Solution: The Model Has an Active Record

Course Assessment

Assessment: Application Development Antipattern

Solution: Use NUMERIC Data Type

Let's solve the antipattern by using the NUMERIC data type.

We'll cover the following

Precision and scale

Instead of FLOAT or its siblings, let’s use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers.

Get hands-on with 1200+ tech skills courses.