SQL Antipatterns
内容简介
Each chapter in this book helps you identify, explain, and correct a unique and dangerous antipattern. The four parts of the book group the antipatterns in terms of logical database design, physical database design, queries, and application development.
The chances are good that your application's database layer already contains problems such as Index Shotgun , Keyless Entry , Fear of the Unknown , and Spaghetti Query . This book will help you and your team find them. Even better, it will also show you how to fix them, and how to avoid these and other problems in the future.
SQL Antipatterns gives you a rare glimpse into an SQL expert's playbook. Now you can stamp out these common database errors once and for all.
Whatever platform or programming language you use, whether you're a junior programmer or a Ph.D., SQL Antipatterns will show you how to design and build databases, how to write better database queries, and how to integrate SQL programming with your application like an expert. You'll also learn the best and most current technology for full-text search, how to design code that is resistant to SQL injection attacks, and other techniques for success.
......(更多)
作者简介
Bill Karwin has been a software engineer for over twenty years, developing and supporting applications, libraries, and servers such as Zend Framework for PHP 5, the InterBase relational database, and the Enhydra Java application server. Throughout his career, Bill has shared his knowledge to help other programmers achieve success and productivity. Bill has answered thousands of questions, giving him a unique perspective on SQL mistakes that most commonly cause problems.
......(更多)
目录
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
......(更多)
读书文摘
在多个列中查找一个值的语法是冗长乏味的。你可以通过一种非传统的方式来使用 IN，从而使得这个查询变得更加精简：
这句查询语句所找到的后代路径分别是 1/4/5、1/4/6 以及 1/4/6/7。
......(更多)
