Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Antipatterns question

 
Razvan Popovici
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have recently read another SQL Antipattern book; most, if not all, of the data structure issues described would go away if a proper normalization (to normal form 3) is performed. So here are my questions:
- are there any database design Antipatterns that go above NF3?
- Is query concatenation (instead of parametric query) an antipattern?
- Another antipattern I see each day is "Query within a loop", and it looks like:



The antipattern's result is a slow program, using a join would increase the speed about 20 times.
 
Bill Karwin
author
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Razvan Popovici wrote:are there any database design Antipatterns that go above NF3?

I have noticed that many online tutorials about database normalization stop at third normal form. But according to one study, 20% of software projects that satisfy 3NF still contain violations of 4NF. So when I wrote an appendix in "SQL Antipatterns" about rules of normalization, I made sure to cover practical examples of database design mistakes that break 4NF and 5NF.

Razvan Popovici wrote:Is query concatenation (instead of parametric query) an antipattern?

Yes, I cover this in a chapter titled SQL Injection. Concatenating dynamic values in an SQL query is a widespread security problem. Attackers have exploited SQL injection vulnerabilities and this has become one of the top threats on the internet. There was a high profile case in 2007 where a hacker used SQL injection to upload his malware to the network of ATM cash machines used by convenience stores. Once he broke in, he was able to steal 145 million credit card and debit card numbers.

Razvan Popovici wrote:Another antipattern I see each day is "Query within a loop" ... using a join would increase the speed about 20 times.

I agree. Some developers listen to oversimplified statements like "joins are expensive" and they take this to mean they must avoid using a join even when they need data from two related tables.

I think more accurate advice would be similar to this famous quote:
Winston Churchill wrote:Indeed, it has been said that democracy is the worst form of government except all those other forms that have been tried from time to time.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic