SQL antipatterns: avoiding the pitfalls of database programming
Gespeichert in:
| 1. Verfasser: | |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
Dallas, Texas ; Raleigh, North Carolina
<<The>> Pragmatic Bookshelf
[2010]
|
| Schriftenreihe: | The Pragmatic Programmers. Database Programming
|
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis |
| Beschreibung: | Hier auch später erschienene, unveränderte Nachdrucke |
| Beschreibung: | xi, 308 Seiten Diagramme |
| ISBN: | 9781934356555 1934356557 |
Internformat
MARC
| LEADER | 00000nam a2200000 c 4500 | ||
|---|---|---|---|
| 001 | BV036592022 | ||
| 003 | DE-604 | ||
| 005 | 20220621 | ||
| 007 | t | ||
| 008 | 100729s2010 |||| |||| 00||| eng d | ||
| 020 | |a 9781934356555 |9 978-1-934356-55-5 | ||
| 020 | |a 1934356557 |9 1-93435-655-7 | ||
| 035 | |a (OCoLC)663865202 | ||
| 035 | |a (DE-599)BVBBV036592022 | ||
| 040 | |a DE-604 |b ger |e rda | ||
| 041 | 0 | |a eng | |
| 049 | |a DE-83 |a DE-11 |a DE-860 |a DE-824 |a DE-355 |a DE-945 |a DE-B768 |a DE-2174 |a DE-859 |a DE-384 |a DE-573 |a DE-703 | ||
| 084 | |a ST 270 |0 (DE-625)143638: |2 rvk | ||
| 084 | |a ST 271 |0 (DE-625)143639: |2 rvk | ||
| 100 | 1 | |a Karwin, Bill |e Verfasser |4 aut | |
| 245 | 1 | 0 | |a SQL antipatterns |b avoiding the pitfalls of database programming |c Bill Karwin |
| 264 | 1 | |a Dallas, Texas ; Raleigh, North Carolina |b <<The>> Pragmatic Bookshelf |c [2010] | |
| 300 | |a xi, 308 Seiten |b Diagramme | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 490 | 0 | |a The Pragmatic Programmers. Database Programming | |
| 500 | |a Hier auch später erschienene, unveränderte Nachdrucke | ||
| 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 |
| 650 | 0 | 7 | |a SQL |0 (DE-588)4134010-3 |2 gnd |9 rswk-swf |
| 689 | 0 | 0 | |a Datenbanksystem |0 (DE-588)4113276-2 |D s |
| 689 | 0 | 1 | |a Datenbanksprache |0 (DE-588)4123038-3 |D s |
| 689 | 0 | 2 | |a SQL |0 (DE-588)4134010-3 |D s |
| 689 | 0 | |5 DE-604 | |
| 856 | 4 | 2 | |m Digitalisierung UB Regensburg |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=020512712&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-020512712 | ||
Datensatz im Suchindex
| _version_ | 1804143189544665088 |
|---|---|
| adam_text | Contents
1
Introduction
1
1.1
Who This Book Is For
.................... 2
1.2
What s in This Book
.................... 3
1.3
What s Not in This Book
.................. 5
1.4
Conventions
......................... 6
1.5
Example Database
..................... 7
1.6
Acknowledgments
...................... 10
1 Logical Database Design Antipatterns
13
2
Jaywalking
15
2.1
Objective: Store Multivalue Attributes
.......... 16
2.2
Antipattern: Format Comma-Separated Lists
...... 16
2.3
How to Recognize the Antipattern
............ 19
2.4
Legitimate Uses of the Antipattern
............ 20
2.5
Solution: Create an Intersection Table
.......... 20
3
Naive Trees
25
3.1
Objective: Store and Query Hierarchies
......... 26
3.2
Antipattern: Always Depend on One s Parent
...... 26
3.3
How to Recognize the Antipattern
............ 30
3.4
Legitimate Uses of the Antipattern
............ 31
3.5
Solution: Use Alternative Tree Models
.......... 32
4
Ш
Required
45
4.1
Objective: Establish Primary Key Conventions
..... 46
4.2
Antipattern: One Size Fits All
............... 48
4.3
How to Recognize the Antipattern
............ 52
4.4
Legitimate Uses of the Antipattern
............ 52
4.5
Solution: Tailored to Fit
.................. 53
viii
► CONTENTS
5
Keyless Entry
57
5.1
Objective: Simplify Database Architecture
........ 58
5.2
Antipattern: Leave Out the Constraints
......... 58
5.3
How to Recognize the Antipattern
............ 61
5.4
Legitimate Uses of the Antipattern
............ 62
5.5
Solution: Declare Constraints
............... 62
β
Entity-Attribute-Value
65
6.1
Objective: Support Variable Attributes
.......... 65
6.2
Antipattern: Use a Generic Attribute Table
....... 66
6.3
How to Recognize the Antipattern
............ 72
6.4
Legitimate Uses of the Antipattern
............ 72
6.5
Solution: Model the Subtypes
............... 74
7
Polymorphic Associations
81
7.1
Objective: Reference Multiple Parents
.......... 82
7.2
Antipattern: Use Dual-Purpose Foreign Key
....... 83
7.3
How to Recognize the Antipattern
............ 86
7.4
Legitimate Uses of the Antipattern
............ 87
7.5
Solution: Simplify the Relationship
............ 88
8
Multicolumn Attributes
95
8.1
Objective: Store Multivalue Attributes
.......... 95
8.2
Antipattern: Create Multiple Columns
.......... 96
8.3
How to Recognize the Antipattern
............ 99
8.4
Legitimate Uses of the Antipattern
............ 100
8.5
Solution: Create Dependent Table
............ 101
9
Metadata Tribbles
103
9.1
Objective: Support Scalability
............... 104
9.2
Antipattern: Clone Tables or Columns
.......... 104
9.3
How to Recognize the Antipattern
............ 109
9.4
Legitimate Uses of the Antipattern
............ 110
9.5
Solution: Partition and Normalize
............
Ill
ix
II Physical Database Design Antipatterns
115
10
Rounding Errors
117
10.1
Objective: Use Fractional Numbers Instead of Integers
118
10.2
Antipattern: Use FLOAT Data Type
............ 118
10.3
How to Recognize the Antipattern
............ 122
10.4
Legitimate Uses of the Antipattern
............ 122
10.5
Solution: Use NUMERIC Data Type
............ 122
11 31
Flavors
125
11.1
Objective: Restrict a Column to Specific Values
.... 125
11.2
Antipattern: Specify Values in the Column Definition
. 126
11.3
How to Recognize the Antipattern
............ 129
11.4
Legitimate Uses of the Antipattern
............ 130
11.5
Solution: Specify Values in Data
............. 130
12
Phantom Files
133
12.1
Objective: Store Images or Other Bulky Media
..... 134
12.2
Antipattern: Assume You Must Use Files
........ 134
12.3
How to Recognize the Antipattern
............ 137
12.4
Legitimate Uses of the Antipattern
............ 138
12.5
Solution: Use BLOB Data Types As Needed
....... 139
13
Index Shotgun
143
13.1
Objective: Optimize Performance
............. 144
13.2
Antipattern: Using Indexes Without a Plan
....... 144
13.3
How to Recognize the Antipattern
............ 148
13.4
Legitimate Uses of the Antipattern
............ 149
13.5
Solution: MENTOR Your Indexes
............. 149
ΠΙ
Query Antipatterns
157
14
Fear of the Unknown
159
14.1
Objective: Distinguish Missing Values
.......... 160
14.2
Antipattern: Use Null as an Ordinary Value, or Vice Versa
160
14.3
How to Recognize the Antipattern
............ 163
14.4
Legitimate Uses of the Antipattern
............ 165
14.5
Solution: Use Null as a Unique Value
.......... 165
CONTENTS
15
Ambiguous Groups
171
15.1
Objective: Get Row with Greatest Value per Group
... 172
15.2
Antipattern: Reference Nongrouped Columns
...... 172
15.3
How to Recognize the Antipattern
............ 174
15.4
Legitimate Uses of the Antipattern
............ 176
15.5
Solution: Use Columns Unambiguously
......... 177
16
Random Selection
181
16.1
Objective: Fetch a Sample Row
.............. 182
16.2
Antipattern: Sort Data Randomly
............. 182
16.3
How to Recognize the Antipattern
............ 183
16.4
Legitimate Uses of the Antipattern
............ 184
16.5
Solution: In No Particular Order
.............. 184
17
Poor Man s Search Engine
189
17.1
Objective: Full-Text Search
................ 190
17.2
Antipattern: Pattern Matching Predicates
........ 190
17.3
How to Recognize the Antipattern
............ 191
17.4
Legitimate Uses of the Antipattern
............ 192
17.5
Solution: Use the Right Tool for the Job
......... 192
18
Spaghetti guery
203
18.1
Objective: Decrease SQL Queries
............. 204
18.2
Antipattern: Solve a Complex Problem in One Step
. . 204
18.3
How to Recognize the Antipattern
............ 206
18.4
Legitimate Uses of the Antipattern
............ 207
18.5
Solution: Divide and Conquer
............... 208
19
Implicit Columns
213
19.1
Objective: Reduce Typing
................. 214
19.2
Antipattern: a Shortcut That Gets You Lost
....... 214
19.3
How to Recognize the Antipattern
............ 216
19.4
Legitimate Uses of the Antipattern
............ 217
19.5
Solution: Name Columns Explicitly
............ 218
χι
IV
Application
Development Antipatterns 221
20
Readable Passwords
223
20.1
Objective: Recover or Reset Passwords
.......... 223
20.2
Antipattern: Store Password in Plain Text
........ 224
20.3
How to Recognize the Antipattern
............ 226
20.4
Legitimate Uses of the Antipattern
............ 226
20.5
Solution: Store a Salted Hash of the Password
..... 228
21
SQL Injection
235
21.1
Objective: Write Dynamic SQL Queries
......... 236
21.2
Antipattern: Execute Unverified Input As Code
..... 236
21.3
How to Recognize the Antipattern
............ 243
21.4
Legitimate Uses of the Antipattern
............ 244
21.5
Solution: Trust No One
................... 244
22
Pseudokey Neat-Freak
251
22.1
Objective: Tidy Up the Data
................ 252
22.2
Antipattern: Filling in the Corners
............ 252
22.3
How to Recognize the Antipattern
............ 255
22.4
Legitimate Uses of the Antipattern
............ 255
22.5
Solution: Get Over It
.................... 255
23
See No Evil
261
23.1
Objective: Write Less Code
................. 262
23.2
Antipattern: Making Bricks Without Straw
....... 262
23.3
How to Recognize the Antipattern
............ 264
23.4
Legitimate Uses of the Antipattern
............ 265
23.5
Solution: Recover from Errors Gracefully
........ 266
24
Diplomatic Immunity
269
24.1
Objective: Employ Best Practices
............. 270
24.2
Antipattern: Make SQL a Second-Class Citizen
..... 270
24.3
How to Recognize the Antipattern
............ 271
24.4
Legitimate Uses of the Antipattern
............ 272
24.5
Solution: Establish a Big-Tent Culture of Quality
... 272
25
Magic Beans
281
25.1
Objective: Simplify Models in MVC
............ 282
25.2
Antipattern: The Model Is an Active Record
....... 283
25.3
How to Recognize the Antipattern
............ 289
25.4
Legitimate Uses of the Antipattern
............ 290
25.5
Solution: The Model Has an Active Record
....... 290
xü
► CONTENTS
V
Appendizes 297
A Rules of Normalization
299
A.1 What Does Relational Mean?
............... 299
A.2 Myths About Normalization
................ 301
A.3 What Is Normalization?
.................. 303
A.4 Common Sense
....................... 313
В
Bibliography
315
Index
317
|
| any_adam_object | 1 |
| author | Karwin, Bill |
| author_facet | Karwin, Bill |
| author_role | aut |
| author_sort | Karwin, Bill |
| author_variant | b k bk |
| building | Verbundindex |
| bvnumber | BV036592022 |
| classification_rvk | ST 270 ST 271 |
| ctrlnum | (OCoLC)663865202 (DE-599)BVBBV036592022 |
| discipline | Informatik |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01756nam a2200409 c 4500</leader><controlfield tag="001">BV036592022</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20220621 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">100729s2010 |||| |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9781934356555</subfield><subfield code="9">978-1-934356-55-5</subfield></datafield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">1934356557</subfield><subfield code="9">1-93435-655-7</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)663865202</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV036592022</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-83</subfield><subfield code="a">DE-11</subfield><subfield code="a">DE-860</subfield><subfield code="a">DE-824</subfield><subfield code="a">DE-355</subfield><subfield code="a">DE-945</subfield><subfield code="a">DE-B768</subfield><subfield code="a">DE-2174</subfield><subfield code="a">DE-859</subfield><subfield code="a">DE-384</subfield><subfield code="a">DE-573</subfield><subfield code="a">DE-703</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 270</subfield><subfield code="0">(DE-625)143638:</subfield><subfield code="2">rvk</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 271</subfield><subfield code="0">(DE-625)143639:</subfield><subfield code="2">rvk</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="b">avoiding the pitfalls of database programming</subfield><subfield code="c">Bill Karwin</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Dallas, Texas ; Raleigh, North Carolina</subfield><subfield code="b"><<The>> Pragmatic Bookshelf</subfield><subfield code="c">[2010]</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">xi, 308 Seiten</subfield><subfield code="b">Diagramme</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. Database Programming</subfield></datafield><datafield tag="500" ind1=" " ind2=" "><subfield code="a">Hier auch später erschienene, unveränderte Nachdrucke</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="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="689" ind1="0" ind2="0"><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="1"><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="2"><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=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">Digitalisierung UB Regensburg</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=020512712&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-020512712</subfield></datafield></record></collection> |
| id | DE-604.BV036592022 |
| illustrated | Not Illustrated |
| indexdate | 2024-07-09T22:43:42Z |
| institution | BVB |
| isbn | 9781934356555 1934356557 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-020512712 |
| oclc_num | 663865202 |
| open_access_boolean | |
| owner | DE-83 DE-11 DE-860 DE-824 DE-355 DE-BY-UBR DE-945 DE-B768 DE-2174 DE-859 DE-384 DE-573 DE-703 |
| owner_facet | DE-83 DE-11 DE-860 DE-824 DE-355 DE-BY-UBR DE-945 DE-B768 DE-2174 DE-859 DE-384 DE-573 DE-703 |
| physical | xi, 308 Seiten Diagramme |
| publishDate | 2010 |
| publishDateSearch | 2010 |
| publishDateSort | 2010 |
| publisher | <<The>> Pragmatic Bookshelf |
| record_format | marc |
| series2 | The Pragmatic Programmers. Database Programming |
| spelling | Karwin, Bill Verfasser aut SQL antipatterns avoiding the pitfalls of database programming Bill Karwin Dallas, Texas ; Raleigh, North Carolina <<The>> Pragmatic Bookshelf [2010] xi, 308 Seiten Diagramme txt rdacontent n rdamedia nc rdacarrier The Pragmatic Programmers. Database Programming Hier auch später erschienene, unveränderte Nachdrucke Datenbanksprache (DE-588)4123038-3 gnd rswk-swf Datenbanksystem (DE-588)4113276-2 gnd rswk-swf SQL (DE-588)4134010-3 gnd rswk-swf Datenbanksystem (DE-588)4113276-2 s Datenbanksprache (DE-588)4123038-3 s SQL (DE-588)4134010-3 s DE-604 Digitalisierung UB Regensburg application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=020512712&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis |
| spellingShingle | Karwin, Bill SQL antipatterns avoiding the pitfalls of database programming Datenbanksprache (DE-588)4123038-3 gnd Datenbanksystem (DE-588)4113276-2 gnd SQL (DE-588)4134010-3 gnd |
| subject_GND | (DE-588)4123038-3 (DE-588)4113276-2 (DE-588)4134010-3 |
| title | SQL antipatterns avoiding the pitfalls of database programming |
| title_auth | SQL antipatterns avoiding the pitfalls of database programming |
| title_exact_search | SQL antipatterns avoiding the pitfalls of database programming |
| title_full | SQL antipatterns avoiding the pitfalls of database programming Bill Karwin |
| title_fullStr | SQL antipatterns avoiding the pitfalls of database programming Bill Karwin |
| title_full_unstemmed | SQL antipatterns 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 | Datenbanksprache (DE-588)4123038-3 gnd Datenbanksystem (DE-588)4113276-2 gnd SQL (DE-588)4134010-3 gnd |
| topic_facet | Datenbanksprache Datenbanksystem SQL |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=020512712&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT karwinbill sqlantipatternsavoidingthepitfallsofdatabaseprogramming |