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

Antipattern: Sort Data Randomly

Let's see the techniques used to sort the data that causes an antipattern.

We'll cover the following

Table scan

The most common SQL trick to pick a random row from a query is to sort the query randomly and pick the first row. This technique is easy to understand and easy to implement:

Get hands-on with 1400+ tech skills courses.