SQL antipatterns: avoiding the pitfalls of database programming 1 Avoiding the pitfalls of database programming
Gespeichert in:
| 1. Verfasser: | |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
Raleigh, North Carolina
The Pragmatic Bookshelf
2022
|
| Ausgabe: | Second edition |
| Schriftenreihe: | The pragmatic programmers
|
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis |
| Beschreibung: | xvii, 340 Seiten |
| ISBN: | 9781680508987 |
Internformat
MARC
| LEADER | 00000nam a2200000 cc4500 | ||
|---|---|---|---|
| 001 | BV049647913 | ||
| 003 | DE-604 | ||
| 005 | 20240514 | ||
| 007 | t | ||
| 008 | 240412s2022 |||| 00||| eng d | ||
| 020 | |a 9781680508987 |9 978-1-68050-898-7 | ||
| 035 | |a (OCoLC)1430766062 | ||
| 035 | |a (DE-599)BVBBV049647913 | ||
| 040 | |a DE-604 |b ger |e rda | ||
| 041 | 0 | |a eng | |
| 049 | |a DE-739 | ||
| 100 | 1 | |a Karwin, Bill |e Verfasser |4 aut | |
| 245 | 1 | 0 | |a SQL antipatterns |n 1 |p Avoiding the pitfalls of database programming |c Bill Karwin |
| 250 | |a Second edition | ||
| 264 | 1 | |a Raleigh, North Carolina |b The Pragmatic Bookshelf |c 2022 | |
| 300 | |a xvii, 340 Seiten | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 490 | 0 | |a The pragmatic programmers | |
| 650 | 0 | 7 | |a SQL |0 (DE-588)4134010-3 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a Datenbanksprache |0 (DE-588)4123038-3 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a Datenbanksystem |0 (DE-588)4113276-2 |2 gnd |9 rswk-swf |
| 689 | 0 | 0 | |a SQL |0 (DE-588)4134010-3 |D s |
| 689 | 0 | 1 | |a Datenbanksystem |0 (DE-588)4113276-2 |D s |
| 689 | 0 | 2 | |a Datenbanksprache |0 (DE-588)4123038-3 |D s |
| 689 | 0 | |5 DE-604 | |
| 773 | 0 | 8 | |w (DE-604)BV049647896 |g 1 |
| 856 | 4 | 2 | |m Digitalisierung UB Passau - ADAM Catalogue Enrichment |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034991407&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 943 | 1 | |a oai:aleph.bib-bvb.de:BVB01-034991407 | |
Datensatz im Suchindex
| _version_ | 1814902708643037184 |
|---|---|
| adam_text |
Contents Acknowledgments . . . . . xiii Introduction. XV 1. What’s an Antipattern?. 1 Types of Antipatterns 1 Anatomy of an Antipattern 2 Entity-Relationship Diagrams 3 Example Database 3 Part I — Logical Database Design Antipatterns 2. Jaywalking.9 Objective: Store Multivalue Attributes 10 Antipattern: Format Comma-Separated Lists 10 How to Recognize the Antipattern 13 Legitimate Uses of the Antipattern 14 Solution: Create an Intersection Table 14 Mlni-Antlpattern: Splitting CSV Into Rows 17 3. Naive Trees.21 Objective: Store and Query Hierarchies 22 Antipattern: Always Depend on One's Parent 22 How to Recognize the Antipattern 26 Legitimate Uses of the Antipattern 26 Solution: Use Alternative Tree Models 27 Mini-Antipattern: It Works on My Computer 39 4. ID Required.41 Objective: Establish Primary Key Conventions 42 Antipattern: One Size Fits All 43
Contents · viii How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: Tailored to Fit Mini-Antipattern: Is a BIGINT Big Enough? 48 48 49 51 5. Keyless Entry. 53 Objective: Simplify Database Architecture 54 Antipattern: Leave Out the Constraints 54 How to Recognize the Antipattern 57 Legitimate Uses of the Antipattern 58 Solution: Declare Constraints 58 6. Entity-Attribute-Value. 61 Objective: Support Variable Attributes 61 Antipattern: Use a Generic Attribute Table 63 How to Recognize the Antipattern 68 Legitimate Uses of the Antipattern 69 Solution: Model the Subtypes 70 7. Polymorphic Associations. 77 Objective: Reference Multiple Parents 78 Antipattern: Use Dual-Purpose Foreign Key 78 How to Recognize the Antlpattem 81 Legitimate Uses of the Antipattern 82 Solution: Simplify the Relationship 83 8. Multicolumn Attributes. 89 Objective: Store Multivalue Attributes 89 Antipattern: Create Multiple Columns 90 How to Recognize the Antlpattem 93 Legitimate Uses of the Antlpattem 94 Solution: Create Dependent Table 94 Mini-Antipattern: Storing Prices 96 9. Metadata Tribbles. 99 Objective: Support Scalability 100 Antipattern: Clone Tables or Columns 100 How to Recognize the Antlpattem 104 Legitimate
Uses of the Antipattern 105 Solution: Partition and Normalize 106
Contents · ίχ Part II — Physical Database Design Antipatterns 10. Rounding Errors. Objective: Use Fractional Numbers Instead of Integers Antipattern: Use FLOAT Data Type How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: Use NUMERIC Data Type 113 114 114 118 118 118 11. 31 Flavors. 121 Objective: Restrict a Column to Specific Values 121 Antipattern: Specify Values in the Column Definition 122 How to Recognize the Antipattern 125 Legitimate Uses of the Antipattern 126 Solution: Specify Values in Data 126 Mini-Antipattern: Reserved Words 128 12. Phantom Files. Objective: Store Images or Other Bulky Media Antipattern: Assume You Must Use Files How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: Use BLOB Data Types As Needed 131 132 132 135 136 137 13. Index Shotgun. Objective: Optimize Performance Antipattern: Using Indexes Without a Plan How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: MENTOR Your Indexes Mini-Antipattern: Indexing Every Column 141 142 142 146 147 147 152 Part III — Query Antipatterns 14. Fear of the Unknown. Objective: Distinguish Missing Values Antipattern: Use Null as an Ordinary Value, or Vice Versa How to Recognize the Antipattern Legitimate Uses of the Antipattern 157 157 158 161 162
Contents · X Solution: Use Null as a Unique Value Mini-Antipattem: NOT IN (NULL) 163 166 15. Ambiguous Groups. Objective: Get Row with Greatest Value per Group Antipattern: Reference Nongrouped Columns How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: Use Columns Unambiguously Mini-Antipattem: Portable SQL 169 170 170 173 173 174 179 16. Random Selection . Objective: Fetch a Sample Row Antipattern: Sort Data Randomly How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: In No Particular Order. Mini-Antipattem: Query for Multiple Random Rows 181 182 182 183 184 184 188 17. Poor Man’s Search Engine.191 Objective: Full-Text Search 191 Antipattern: Pattern Matching Predicates 192 How to Recognize the Antipattern 193 Legitimate Uses of the Antipattern 193 Solution: Use the Right Tool for the Job 194 18. Spaghetti Query. 205 Objective: Decrease SQL Queries 206 Antipattern: Solve a Complex Problem in One Step 206 How to Recognize the Antipattern 208 Legitimate Uses of the Antipattern 209 Solution: Divide and Conquer 211 19. Implicit Columns. Objective: Reduce Typing Antipattern: A Shortcut That Gets You Lost How to Recognize the Antipattern Legitimate Uses of the Antipattern Solution: Name Columns Explicitly . 215 216 216 219 219 220
Contents · xi Part IV — Application Development Antipatterns 20. Readable Passwords.225 Objective: Recover or Reset Passwords 225 Antipattern: Store Password in Plain Text 226 How to Recognize the Antipattern 228 Legitimate Uses of the Antipattern 229 Solution: Store a Salted Hash of the Password 230 Mini-Antipattern: Storing Hash Strings in VARCHAR 237 21. SQL Injection.239 Objective: Write Dynamic SQL Queries 240 Antipattern: Execute Unverified Input As Code 240 How to Recognize the Antipattern 247 Legitimate Uses of the Antipattern 248 Solution: Trust No One 248 Mini-Antipattern: Query Parameters inside Quotes 255 22. Pseudokey Neat-Freak.257 Objective: Tidy Up the Data 258 Antipattern: Filling in the Corners 258 How to Recognize the Antipattern 260 Legitimate Uses of the Antipattern 261 Solution: Get Over It 261 Mini-Antipattern: Auto-Increment per Group 264 23. See No Evil. .265 Objective: Write Less Code 266 Antipattern: Making Bricks Without Straw 266 How to Recognize the Antipattern 269 Legitimate Uses of the Antipattern 269 Solution: Recover from Errors Gracefully 269 Minl-Antlpattern: Reading Syntax Error Messages 271 24. Diplomatic Immunity.273 Objective: Employ Best Practices 273 Antipattern:
Make SQL a Second-Class Citizen 274 How to Recognize the Antipattern 275 Legitimate Uses of the Antipattern 276 Solution: Establish a Big-Tent Culture of Quality 276 Mini-Antipattern: Renaming Things 284
Contents · xii 25. Standard Operating Procedures. 287 Objective: Use Stored Procedures 288 Antipattern: Follow the Leader 289 How to Recognize the Antipattern 292 Legitimate Uses of the Antipattern 293 Solution: Adopt Modern Application Architecture 294 Mini-Antipattern: Stored Procedures in MySQL 295 Part V — Bonus: More Foreign Key Mini-Antipatterns 26. Foreign Key Mistakes in Standard SQL. 301 Reversing the Direction of Reference 301 Referencing Tables Before They Have Been Created 302 Referencing No Key of the Parent Table 304 Creating Separate Constraints for Each Column in a Compound Key 304 Using the Wrong Column Order 305 Using Mismatched Data Types 306 Using Mismatched Character Collations 308 Creating Orphan Data 309 Using the SET NULL Option for Non-Nullable Columns 310 Making Duplicate Constraint Identifiers 311 Using Incompatible Table Types 312 27. Foreign Key Mistakes in MySQL. 313 Using Incompatible Storage Engines 313 Using Large Data Types 314 MySQL Foreign Keys to Non-Unique Indexes 315 Using Inline References Syntax 317 Using Default References Syntax 318 Using Incompatible Table Types in MySQL 319 Al. Rules of Normalization. 321 What Does Relational Mean? 321 Myths About Normalization 323 What Is Normalization? 324 Common Sense 335 Bibliography. 337
Index. 339 |
| adam_txt | |
| any_adam_object | 1 |
| any_adam_object_boolean | |
| author | Karwin, Bill |
| author_facet | Karwin, Bill |
| author_role | aut |
| author_sort | Karwin, Bill |
| author_variant | b k bk |
| building | Verbundindex |
| bvnumber | BV049647913 |
| ctrlnum | (OCoLC)1430766062 (DE-599)BVBBV049647913 |
| edition | Second edition |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>00000nam a2200000 cc4500</leader><controlfield tag="001">BV049647913</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20240514</controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">240412s2022 |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9781680508987</subfield><subfield code="9">978-1-68050-898-7</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)1430766062</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV049647913</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">rda</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="049" ind1=" " ind2=" "><subfield code="a">DE-739</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Karwin, Bill</subfield><subfield code="e">Verfasser</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">SQL antipatterns</subfield><subfield code="n">1</subfield><subfield code="p">Avoiding the pitfalls of database programming</subfield><subfield code="c">Bill Karwin</subfield></datafield><datafield tag="250" ind1=" " ind2=" "><subfield code="a">Second edition</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Raleigh, North Carolina</subfield><subfield code="b">The Pragmatic Bookshelf</subfield><subfield code="c">2022</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">xvii, 340 Seiten</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="490" ind1="0" ind2=" "><subfield code="a">The pragmatic programmers</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Datenbanksprache</subfield><subfield code="0">(DE-588)4123038-3</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Datenbanksystem</subfield><subfield code="0">(DE-588)4113276-2</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="689" ind1="0" ind2="0"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2="1"><subfield code="a">Datenbanksystem</subfield><subfield code="0">(DE-588)4113276-2</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2="2"><subfield code="a">Datenbanksprache</subfield><subfield code="0">(DE-588)4123038-3</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="773" ind1="0" ind2="8"><subfield code="w">(DE-604)BV049647896</subfield><subfield code="g">1</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">Digitalisierung UB Passau - ADAM Catalogue Enrichment</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034991407&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="943" ind1="1" ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-034991407</subfield></datafield></record></collection> |
| id | DE-604.BV049647913 |
| illustrated | Not Illustrated |
| index_date | 2024-07-03T23:40:04Z |
| indexdate | 2024-11-05T17:01:39Z |
| institution | BVB |
| isbn | 9781680508987 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-034991407 |
| oclc_num | 1430766062 |
| open_access_boolean | |
| owner | DE-739 |
| owner_facet | DE-739 |
| physical | xvii, 340 Seiten |
| publishDate | 2022 |
| publishDateSearch | 2022 |
| publishDateSort | 2022 |
| publisher | The Pragmatic Bookshelf |
| record_format | marc |
| series2 | The pragmatic programmers |
| spelling | Karwin, Bill Verfasser aut SQL antipatterns 1 Avoiding the pitfalls of database programming Bill Karwin Second edition Raleigh, North Carolina The Pragmatic Bookshelf 2022 xvii, 340 Seiten txt rdacontent n rdamedia nc rdacarrier The pragmatic programmers SQL (DE-588)4134010-3 gnd rswk-swf Datenbanksprache (DE-588)4123038-3 gnd rswk-swf Datenbanksystem (DE-588)4113276-2 gnd rswk-swf SQL (DE-588)4134010-3 s Datenbanksystem (DE-588)4113276-2 s Datenbanksprache (DE-588)4123038-3 s DE-604 (DE-604)BV049647896 1 Digitalisierung UB Passau - ADAM Catalogue Enrichment application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034991407&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis |
| spellingShingle | Karwin, Bill SQL antipatterns SQL (DE-588)4134010-3 gnd Datenbanksprache (DE-588)4123038-3 gnd Datenbanksystem (DE-588)4113276-2 gnd |
| subject_GND | (DE-588)4134010-3 (DE-588)4123038-3 (DE-588)4113276-2 |
| title | SQL antipatterns |
| title_auth | SQL antipatterns |
| title_exact_search | SQL antipatterns |
| title_exact_search_txtP | SQL antipatterns, volume 1 avoiding the pitfalls of database programming |
| title_full | SQL antipatterns 1 Avoiding the pitfalls of database programming Bill Karwin |
| title_fullStr | SQL antipatterns 1 Avoiding the pitfalls of database programming Bill Karwin |
| title_full_unstemmed | SQL antipatterns 1 Avoiding the pitfalls of database programming Bill Karwin |
| title_short | SQL antipatterns |
| title_sort | sql antipatterns avoiding the pitfalls of database programming |
| title_sub | avoiding the pitfalls of database programming |
| topic | SQL (DE-588)4134010-3 gnd Datenbanksprache (DE-588)4123038-3 gnd Datenbanksystem (DE-588)4113276-2 gnd |
| topic_facet | SQL Datenbanksprache Datenbanksystem |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034991407&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| volume_link | (DE-604)BV049647896 |
| work_keys_str_mv | AT karwinbill sqlantipatterns1 |