SQL antipatterns : avoiding the pitfalls of database programming / Bill Karwin.

Format:

Book

Author/Creator:

Karwin, Bill, author.

Contributor:

Carter, Jacquelyn, editor.

Series:

Pragmatic programmers SQL antipatterns

Language:

English

Subjects (All):

SQL (Computer program language).

Antipatterns (Software engineering).

Physical Description:

1 online resource (314 pages) : illustrations

Edition:

1st edition

Place of Publication:

Dallas, Texas ; Raleigh, North Carolina : The Pragmatic Bookself, 2010.

Language Note:

English

System Details:

Mode of access: World Wide Web.

text file

Summary:

Bill Karwin has helped thousands of people write better SQL and buildstronger relational databases. Now he's sharing his collection of antipatterns--the most common errors he's identified in those thousands of requests for help. Most developers aren't SQL experts, and most of the SQL that gets used is inefficient, hard to maintain, and sometimes just plain wrong. This book shows you all the common mistakes, and then leads you through the best fixes. What's more, it shows you what's behind these fixes, so you'll learn a lot about relational databases along the way.

Contents:

Cover

Table of Contents

1. Introduction

Who This Book Is For

What's in This Book

What's Not in This Book

Conventions

Example Database

Acknowledgments

Part I-Logical Database Design Antipatterns

2. Jaywalking

Objective: Store Multivalue Attributes

Antipattern: Format Comma-Separated Lists

How to Recognize the Antipattern

Legitimate Uses of the Antipattern

Solution: Create an Intersection Table

3. Naive Trees

Objective: Store and Query Hierarchies

Antipattern: Always Depend on One's Parent

Solution: Use Alternative Tree Models

4. ID Required

Objective: Establish Primary Key Conventions

Antipattern: One Size Fits All

Solution: Tailored to Fit

5. Keyless Entry

Objective: Simplify Database Architecture

Antipattern: Leave Out the Constraints

Solution: Declare Constraints

6. Entity-Attribute-Value

Objective: Support Variable Attributes

Antipattern: Use a Generic Attribute Table

Solution: Model the Subtypes

7. Polymorphic Associations

Objective: Reference Multiple Parents

Antipattern: Use Dual-Purpose Foreign Key

Solution: Simplify the Relationship

8. Multicolumn Attributes

Antipattern: Create Multiple Columns

Solution: Create Dependent Table

9. Metadata Tribbles

Objective: Support Scalability

Antipattern: Clone Tables or Columns.

How to Recognize the Antipattern

Solution: Partition and Normalize

Part II-Physical Database Design Antipatterns

10. Rounding Errors

Objective: Use Fractional Numbers Instead of Integers

Antipattern: Use FLOAT Data Type

Solution: Use NUMERIC Data Type

11. 31 Flavors

Objective: Restrict a Column to Specific Values

Antipattern: Specify Values in the Column Definition

Solution: Specify Values in Data

12. Phantom Files

Objective: Store Images or Other Bulky Media

Antipattern: Assume You Must Use Files

Solution: Use BLOB Data Types As Needed

13. Index Shotgun

Objective: Optimize Performance

Antipattern: Using Indexes Without a Plan

Solution: MENTOR Your Indexes

Part III-Query Antipatterns

14. Fear of the Unknown

Objective: Distinguish Missing Values

Antipattern: Use Null as an Ordinary Value, or Vice Versa

Solution: Use Null as a Unique Value

15. Ambiguous Groups

Objective: Get Row with Greatest Value per Group

Antipattern: Reference Nongrouped Columns

Solution: Use Columns Unambiguously

16. Random Selection

Objective: Fetch a Sample Row

Antipattern: Sort Data Randomly

Solution: In No Particular Order…

17. Poor Man's Search Engine

Objective: Full-Text Search

Antipattern: Pattern Matching Predicates.

Solution: Use the Right Tool for the Job

18. Spaghetti Query

Objective: Decrease SQL Queries

Antipattern: Solve a Complex Problem in One Step

Solution: Divide and Conquer

19. Implicit Columns

Objective: Reduce Typing

Antipattern: a Shortcut That Gets You Lost

Solution: Name Columns Explicitly

Part IV-Application Development Antipatterns

20. Readable Passwords

Objective: Recover or Reset Passwords

Antipattern: Store Password in Plain Text

Solution: Store a Salted Hash of the Password

21. SQL Injection

Objective: Write Dynamic SQL Queries

Antipattern: Execute Unverified Input As Code

Solution: Trust No One

22. Pseudokey Neat-Freak

Objective: Tidy Up the Data

Antipattern: Filling in the Corners

Solution: Get Over It

23. See No Evil

Objective: Write Less Code

Antipattern: Making Bricks Without Straw

Solution: Recover from Errors Gracefully

24. Diplomatic Immunity

Objective: Employ Best Practices

Antipattern: Make SQL a Second-Class Citizen

Solution: Establish a Big-Tent Culture of Quality

25. Magic Beans

Objective: Simplify Models in MVC

Antipattern: The Model Is an Active Record

Legitimate Uses of the Antipattern.

Solution: The Model Has an Active Record

Part V-Appendixes

A1. Rules of Normalization

What Does Relational Mean?

Myths About Normalization

What Is Normalization?

Common Sense

A2. Bibliography

Index

- SYMBOLS -

- A -

- B -

- C -

- D -

- E -

- F -

- G -

- H -

- I -

- J -

- K -

- L -

- M -

- N -

- O -

- P -

- Q -

- R -

- S -

- T -

- U -

- V -

- W -

- Z -.

Notes:

Includes index.

Includes bibliographical references.

Description based on print version record.

ISBN:

9781680500073

1680500074

9781680504804

1680504800

9781680504811

1680504819

9781934356555

1934356557

OCLC:

897116867