1 Introduction
1.1 Who This Book Is For
1.2 What’s in This Book
1.3 What’s Not in This Book
1.4 Conventions
1.5 Example Database
1.6 Acknowledgements
Logical Database Design Antipatterns
2 Jaywalking
2.1 Objective: Store Multi-Value Attributes
2.2 Antipattern: Format Comma-Separated Lists
2.3 How to Recognize the Antipattern
2.4 Legitimate Uses of the Antipattern
2.5 Solution: Create an Intersection Table
3 Naive Trees
3.1 Objective: Store and Query Hierarchies
3.2 Antipattern: Always Depend on One’s Parent
3.3 How to Recognize the Antipattern
3.4 Legitimate Uses of the Antipattern
3.5 Solution: Use Alternative Tree Models
4 ID Required
4.1 Objective: Establish Primary Key Conventions
4.2 Antipattern: One Size Fits All
4.3 How to Recognize the Antipattern
4.4 Legitimate Uses of the Antipattern
4.5 Solution:Tailored to Fit
5 Keyless Entry
5.1 Objective: simplify database architecture
5.2 Antipattern: leave out the constraints
5.3 How to Recognize the Antipattern
5.4 Legitimate Uses of the Antipattern
5.5 Solution:declare constraints
6 Entity-Attribute-Value
6.1 Objective: Support Variable Attributes
6.2 Antipattern: Use a Generic Attribute Table
6.3 How to Recognize the Antipattern
6.4 Legitimate Uses of the Antipattern
6.5 Solution:Model the Subtypes
7 Polymorphic Associations
7.1 Objective: Reference Multiple Parents
7.2 Antipattern: Use Dual-Purpose Foreign Key
7.3 How to Recognize the Antipattern
7.4 Legitimate Uses of the Antipattern
7.5 Solution:Simplify the Relationship
8 Multi-Column Attributes
8.1 Objective: Store Multi-Value Attributes
8.2 Antipattern: Create Multiple Columns
8.3 How to Recognize the Antipattern
8.4 Legitimate Uses of the Antipattern
8.5 Solution:Create Dependent Table
9 Metadata Tribbles
9.1 Objective:Support Scalability
9.2 Antipattern:Clone Tables or Columns
9.3 How to Recognize the Antipattern
9.4 Legitimate Uses of the Antipattern
9.5 Solution:Partition and Normalize
II Physical Database Design Antipatterns
10 Rounding Errors
10.1 Objective: Use Fractional Numbers Instead of Integers
10.2 Antipattern:Use FLOAT DataType
10.3 How to Recognize the Antipattern
10.4 Legitimate Uses of the Antipattern
10.5 Solution:Use NUMERIC DataType
11 Flavors
11.1 Objective: Restrict a Column to Specific Values
11.2 Antipattern: Specify Values in the Column Definition
11.3 How to Recognize the Antipattern
11.4 Legitimate Uses of the Antipattern
11.5 Solution:Specify Values in Data
12 Phantom Files
12.1 Objective: Store Images or Other Bulky Media
12.2 Antipattern: Assume You Must Use Files
12.3 How to Recognize the Antipattern
12.4 Legitimate Uses of the Antipattern
12.5 Solution: Use BLOB Data Types As Needed
13 Index Shotgun
13.1 Objective:OptimizePerformance
13.2 Antipattern: Using Indexes Without a Plan
13.3 How to Recognize the Antipattern
13.4 Legitimate Uses of the Antipattern
13.5 Solution:MENTOR Your Indexes
III Query Antipatterns
14 Fear of the Unknown
14.1 Objective: Distinguish Missing Values
14.2 Antipattern: Use Null as Ordinary Value or Vice Versa
14.3 How to Recognize the Antipattern
14.4 Legitimate Uses of the Antipattern
14.5 Solution:Use Null as a Unique Value
15 Ambiguous Groups
15.1 Objective: Get Row with Greatest Value per Group
15.2 Antipattern: Reference Non-Grouped Columns
15.3 How to Recognize the Antipattern
15.4 Legitimate Uses of the Antipattern
15.5 Solution: Use Columns Unambiguously
16 Random Selection
16.1 Objective:FetchASampleRow
16.2 Antipattern:SortDataRandomly
16.3 How to Recognize the Antipattern
16.4 Legitimate Uses of the Antipattern
16.5 Solution:In No Particular Order
17 Poor Man’s Search Engine
17.1 Objective:FullTextSearch
17.2 Antipattern: Pattern Matching Predicates
17.3 How to Recognize the Antipattern
17.4 Legitimate Uses of the Antipattern
17.5 Solution:Use the Right Tool for the Job
18 Spaghetti Query
18.1 Objective:DecreaseSQLQueries
18.2 Antipattern: Solve a Complex Problem in One Step
18.3 How to Recognize the Antipattern
18.4 Legitimate Uses of the Antipattern
18.5 Solution:Divide and Conquer
19 Implicit Columns
19.1 Objective:ReduceTyping
19.2 Antipattern: a Short Cut That Gets You Lost
19.3 How to Recognize the Antipattern
19.4 Legitimate Uses of the Antipattern
19.5 Solution:Name Columns Explicitly
IV Application Development Antipatterns
20 Readable Passwords
20.1 Objective: Recover or Reset Passwords
20.2 Antipattern: Store Password in Plain Text
20.3 How to Recognize the Antipattern
20.4 Legitimate Uses of the Antipattern
20.5 Solution: Store a Salted Hash of the Password
21 SQL Injection
21.1 Objective: Write Dynamic SQL Queries
21.2 Antipattern: Execute Unverified Input As Code
21.3 How to Recognize the Antipattern
21.4 Legitimate Uses of the Antipattern
21.5 Solution:Trust No One
22 Pseudokey Neat-Freak
22.1 Objective:Tidy Up the Data
22.2 Antipattern:Filling in the Corners
22.3 How to Recognize the Antipattern
22.4 Legitimate Uses of the Antipattern
22.5 Solution:Get Over It
23 See No Evil
23.1 Objective:Write Less Code
23.2 Antipattern: Making Bricks Without Straw
23.3 How to Recognize the Antipattern
23.4 Legitimate Uses of the Antipattern
23.5 Solution: Recover from Errors Gracefully
24 Diplomatic Immunity
24.1 Objective:Employ Best Practices
24.2 Antipattern: Make SQL a Second-Class Citizen
24.3 How to Recognize the Antipattern
24.4 Legitimate Uses of the Antipattern
24.5 Solution: Establish a Big-Tent Culture of Quality
25 Magic Beans
25.1 Objective:Simplify Mode ls in MVC
25.2 Antipattern: The Model Is an Active Record
25.3 How to Recognize the Antipattern
25.4 Legitimate Uses of the Antipattern
25.5 Solution: The Model Has an Active Record
V Appendixes
A Rules of Normalization
A.1 What Does Relational Mean?
A.2 Myths About Normalization
A.3 What Is Normalization?
A.4 Common Sense
B Bibliography
Index