SQL Antipatterns - The Pragmatic Programmer
Extracted from:SQL AntipatternsAvoiding the Pitfalls of Database ProgrammingThis PDF file contains pages extracted from SQL Antipatterns, published by thePragmatic Bookshelf. For more information or to purchase a paperback or PDFcopy, please visit http://www.pragprog.com.Note: This extract contains some colored text (particularly in code listing). Thisis available only in online versions of the books. The printed versions are blackand white. Pagination might vary between the online and printer versions; thecontent is otherwise identical.Copyright 2010 The Pragmatic Programmers, LLC.All rights reserved.No part of this publication may be reproduced, stored in a retrieval system, or transmitted,in any form, or by any means, electronic, mechanical, photocopying, recording, or otherwise,without the prior consent of the publisher.The Pragmatic BookshelfDallas, Texas Raleigh, North Carolina
SQL AntipatternsAvoiding the Pitfalls of Database ProgrammingBill KarwinThe Pragmatic BookshelfDallas, Texas Raleigh, North Carolina
Many of the designations used by manufacturers and sellers to distinguish their productsare claimed as trademarks. Where those designations appear in this book, and The PragmaticProgrammers, LLC was aware of a trademark claim, the designations have been printed ininitial capital letters or in all capitals. The Pragmatic Starter Kit, The Pragmatic Programmer,Pragmatic Programming, Pragmatic Bookshelf, PragProg and the linking g device are trademarks of The Pragmatic Programmers, LLC.Every precaution was taken in the preparation of this book. However, the publisher assumesno responsibility for errors or omissions, or for damages that may result from the use ofinformation (including program listings) contained herein.Our Pragmatic courses, workshops, and other products can help you and your team createbetter software and have more fun. For more information, as well as the latest Pragmatictitles, please visit us at http://pragprog.com.Copyright 2010 Bill Karwin.All rights reserved.No part of this publication may be reproduced, stored in a retrieval system, ortransmitted, in any form, or by any means, electronic, mechanical, photocopying,recording, or otherwise, without the prior consent of the publisher.Printed in the United States of America.ISBN-13: 978-1-934356-55-5Encoded using the finest acid-free high-entropy binary digits.Book version: P3.0—March 2012
Contents1.Introduction .1.1 Who This Book Is For1.2 What’s in This Book1.3 What’s Not in This Book1.4 Conventions1.5 Example Database1.6 Acknowledgments.?Part I — Logical Database Design Antipatterns2.Jaywalking.2.1 Objective: Store Multivalue Attributes2.2 Antipattern: Format Comma-Separated Lists2.3 How to Recognize the Antipattern2.4 Legitimate Uses of the Antipattern2.5 Solution: Create an Intersection Table.?3.Naive3.13.23.33.43.5Trees.Objective: Store and Query HierarchiesAntipattern: Always Depend on One’s ParentHow to Recognize the AntipatternLegitimate Uses of the AntipatternSolution: Use Alternative Tree Models.?4.ID Required .4.1 Objective: Establish Primary Key Conventions4.2 Antipattern: One Size Fits All4.3 How to Recognize the Antipattern4.4 Legitimate Uses of the Antipattern4.5 Solution: Tailored to Fit.?
Contents v5.Keyless Entry .5.1 Objective: Simplify Database Architecture5.2 Antipattern: Leave Out the Constraints5.3 How to Recognize the Antipattern5.4 Legitimate Uses of the Antipattern5.5 Solution: Declare Constraints.?6.Entity-Attribute-Value.6.1 Objective: Support Variable Attributes6.2 Antipattern: Use a Generic Attribute Table6.3 How to Recognize the Antipattern6.4 Legitimate Uses of the Antipattern6.5 Solution: Model the Subtypes.?7.Polymorphic Associations.7.1 Objective: Reference Multiple Parents7.2 Antipattern: Use Dual-Purpose Foreign Key7.3 How to Recognize the Antipattern7.4 Legitimate Uses of the Antipattern7.5 Solution: Simplify the Relationship.?8.Multicolumn Attributes.8.1 Objective: Store Multivalue Attributes8.2 Antipattern: Create Multiple Columns8.3 How to Recognize the Antipattern8.4 Legitimate Uses of the Antipattern8.5 Solution: Create Dependent Table.?9.Metadata Tribbles .9.1 Objective: Support Scalability9.2 Antipattern: Clone Tables or Columns9.3 How to Recognize the Antipattern9.4 Legitimate Uses of the Antipattern9.5 Solution: Partition and Normalize.?10. Rounding Errors .10.1 Objective: Use Fractional Numbers Instead of Integers10.2 Antipattern: Use FLOAT Data Type10.3 How to Recognize the Antipattern.?Part II — Physical Database Design Antipatterns
Contents10.4 Legitimate Uses of the Antipattern10.5 Solution: Use NUMERIC Data Type vi?11. 31 Flavors.11.1 Objective: Restrict a Column to Specific Values11.2 Antipattern: Specify Values in the Column Definition11.3 How to Recognize the Antipattern11.4 Legitimate Uses of the Antipattern11.5 Solution: Specify Values in Data.?12. Phantom Files.12.1 Objective: Store Images or Other Bulky Media12.2 Antipattern: Assume You Must Use Files12.3 How to Recognize the Antipattern12.4 Legitimate Uses of the Antipattern12.5 Solution: Use BLOB Data Types As Needed.?13. Index Shotgun.13.1 Objective: Optimize Performance13.2 Antipattern: Using Indexes Without a Plan13.3 How to Recognize the Antipattern13.4 Legitimate Uses of the Antipattern13.5 Solution: MENTOR Your Indexes.?14. Fear of the Unknown .14.1 Objective: Distinguish Missing Values14.2 Antipattern: Use Null as an Ordinary Value, or ViceVersa14.3 How to Recognize the Antipattern14.4 Legitimate Uses of the Antipattern14.5 Solution: Use Null as a Unique Value?15. Ambiguous Groups.15.1 Objective: Get Row with Greatest Value per Group15.2 Antipattern: Reference Nongrouped Columns15.3 How to Recognize the Antipattern15.4 Legitimate Uses of the Antipattern15.5 Solution: Use Columns Unambiguously?.Part III — Query Antipatterns.?
Contents16. Random Selection.16.1 Objective: Fetch a Sample Row16.2 Antipattern: Sort Data Randomly16.3 How to Recognize the Antipattern16.4 Legitimate Uses of the Antipattern16.5 Solution: In No Particular Order .?Man’s Search Engine.Objective: Full-Text SearchAntipattern: Pattern Matching PredicatesHow to Recognize the AntipatternLegitimate Uses of the AntipatternSolution: Use the Right Tool for the Job.?18. Spaghetti Query .18.1 Objective: Decrease SQL Queries18.2 Antipattern: Solve a Complex Problem in One Step18.3 How to Recognize the Antipattern18.4 Legitimate Uses of the Antipattern18.5 Solution: Divide and Conquer.?19. Implicit Columns .19.1 Objective: Reduce Typing19.2 Antipattern: a Shortcut That Gets You Lost19.3 How to Recognize the Antipattern19.4 Legitimate Uses of the Antipattern19.5 Solution: Name Columns Explicitly.?17. Poor17.117.217.317.417.5. vii.Part IV — Application Development Antipatterns20. Readable Passwords .20.1 Objective: Recover or Reset Passwords20.2 Antipattern: Store Password in Plain Text20.3 How to Recognize the Antipattern20.4 Legitimate Uses of the Antipattern20.5 Solution: Store a Salted Hash of the Password.?21. SQL Injection .21.1 Objective: Write Dynamic SQL Queries21.2 Antipattern: Execute Unverified Input As Code21.3 How to Recognize the Antipattern.?
viii Contents21.4 Legitimate Uses of the Antipattern21.5 Solution: Trust No One22. Pseudokey Neat-Freak.22.1 Objective: Tidy Up the Data22.2 Antipattern: Filling in the Corners22.3 How to Recognize the Antipattern22.4 Legitimate Uses of the Antipattern22.5 Solution: Get Over It?.?23. See No Evil.23.1 Objective: Write Less Code23.2 Antipattern: Making Bricks Without Straw23.3 How to Recognize the Antipattern23.4 Legitimate Uses of the Antipattern23.5 Solution: Recover from Errors Gracefully.?24. Diplomatic Immunity .24.1 Objective: Employ Best Practices24.2 Antipattern: Make SQL a Second-Class Citizen24.3 How to Recognize the Antipattern24.4 Legitimate Uses of the Antipattern24.5 Solution: Establish a Big-Tent Culture of Quality.?25. Magic Beans .25.1 Objective: Simplify Models in MVC25.2 Antipattern: The Model Is an Active Record25.3 How to Recognize the Antipattern25.4 Legitimate Uses of the Antipattern25.5 Solution: The Model Has an Active Record.?Part V — AppendixesA1. Rules of Normalization.A1.1 What Does Relational Mean?A1.2 Myths About NormalizationA1.3 What Is Normalization?A1.4 Common Sense.?A2. Bibliography .Index .?.?
initial capital letters or in all capitals. The Pragmatic Starter Kit, The Pragmatic Programmer, Pragmatic Programming, Pragmatic Bookshelf, PragProg and the linking g device are trade-marks of The Pragmatic Programmers, LLC. Every precaution was taken in the preparation of this book. However, the publisher assumes
May 02, 2018 · D. Program Evaluation ͟The organization has provided a description of the framework for how each program will be evaluated. The framework should include all the elements below: ͟The evaluation methods are cost-effective for the organization ͟Quantitative and qualitative data is being collected (at Basics tier, data collection must have begun)
Silat is a combative art of self-defense and survival rooted from Matay archipelago. It was traced at thé early of Langkasuka Kingdom (2nd century CE) till thé reign of Melaka (Malaysia) Sultanate era (13th century). Silat has now evolved to become part of social culture and tradition with thé appearance of a fine physical and spiritual .
On an exceptional basis, Member States may request UNESCO to provide thé candidates with access to thé platform so they can complète thé form by themselves. Thèse requests must be addressed to esd rize unesco. or by 15 A ril 2021 UNESCO will provide thé nomineewith accessto thé platform via their émail address.
̶The leading indicator of employee engagement is based on the quality of the relationship between employee and supervisor Empower your managers! ̶Help them understand the impact on the organization ̶Share important changes, plan options, tasks, and deadlines ̶Provide key messages and talking points ̶Prepare them to answer employee questions
Dr. Sunita Bharatwal** Dr. Pawan Garga*** Abstract Customer satisfaction is derived from thè functionalities and values, a product or Service can provide. The current study aims to segregate thè dimensions of ordine Service quality and gather insights on its impact on web shopping. The trends of purchases have
Chính Văn.- Còn đức Thế tôn thì tuệ giác cực kỳ trong sạch 8: hiện hành bất nhị 9, đạt đến vô tướng 10, đứng vào chỗ đứng của các đức Thế tôn 11, thể hiện tính bình đẳng của các Ngài, đến chỗ không còn chướng ngại 12, giáo pháp không thể khuynh đảo, tâm thức không bị cản trở, cái được
SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact- SQL). T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capab
Vol.10, No.8, 2018 3 Annual Book of ASTM Standards (1986), “Standard Test Method for Static Modulus of Elasticity and Poissons’s Ratio of Concrete in Compression”, ASTM C 469-83, Volume 04.02, 305-309. Table 1. Dimensions of a typical concrete block units used in the construction of the prisms Construction Method a (mm) b
