aspose file tools*
The moose likes JDBC and the fly likes SQL Antipatterns question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Antipatterns question" Watch "SQL Antipatterns question" New topic
Author

SQL Antipatterns question

Razvan Popovici
Greenhorn

Joined: Feb 15, 2007
Posts: 14
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

Joined: Aug 02, 2010
Posts: 32
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.


Bill Karwin is the author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Antipatterns question